excel vba

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft VBA Training and help » Excel VBA

Excel VBA

resolvedResolved · High Priority · Version 2007

Edited on Fri 21 Oct 2011, 09:30

Steve has attended:
Excel VBA Intro Intermediate course

Excel VBA

When I loop through a sheet to copy data to a target sheet depending on whether certain criteria is found, the target data is being copied with gaps.
If my data is in rows 1 to 30 and in Column A theres is a code from 1 to 30 on which the criteris is based, If I copy code 1 to 11, this is palced in rows 1 to 11. Then if I copy code 21 to 29, this is placed in rows 21 onwards, where as I want it to be placed immediately after the first set of data, i.e row 12. This is the code I am using. Where am i going wrong?

Dim intTargetRowCount As Integer
Dim intNumRows As Integer
Dim intRowCount As Integer
Dim intColumnCount As Integer


Sub Copydata()

intTargetRowCount = 1

intNumRows = Sheets("Workings").Range("A1").CurrentRegion.Rows.Count

For intRowCount = 1 To intNumRows

For intColumnCount = 1 To 3

If Sheets("Workings").Range("A1").Cells(intRowCount, 1).Value < 12 Then

Sheets("Test").Cells(intTargetRowCount, intColumnCount).Value = _
Sheets("Workings").Range("A1").Cells(intRowCount, intColumnCount).Value

End If

If Sheets("Workings").Range("A1").Cells(intRowCount, 1).Value > 20 And _
Sheets("Workings").Range("A1").Cells(intRowCount, 1).Value < 30 Then

Sheets("Test").Cells(intTargetRowCount, intColumnCount).Value = _
Sheets("Workings").Range("A1").Cells(intRowCount, intColumnCount).Value

End If
Next intColumnCount
intTargetRowCount = intTargetRowCount + 1

Next intRowCount
End Sub

RE: Excel VBA

Hello Steve,

Try this, the column counts need their own for-next loop and targetrowcounts, where you had this, it was counting the target rows even if there was no criteria match. Hope this works: by the way, put the sheet1 names back to youor Workings sheet name!!!!!

Sub Copydata()

intTargetRowCount = 1

intNumRows = Sheets("sheet1").Range("A1").CurrentRegion.Rows.Count

For intRowCount = 1 To intNumRows



If Sheets("sheet1").Range("A1").Cells(intRowCount, 1).Value < 12 Then
For intColumnCount = 1 To 3
Sheets("Test").Cells(intTargetRowCount, intColumnCount).Value = _
Sheets("sheet1").Range("A1").Cells(intRowCount, intColumnCount).Value
Next intColumnCount
intTargetRowCount = intTargetRowCount + 1

ElseIf Sheets("sheet1").Range("A1").Cells(intRowCount, 1).Value > 20 And _
Sheets("sheet1").Range("A1").Cells(intRowCount, 1).Value < 30 Then

For intColumnCount = 1 To 3
Sheets("Test").Cells(intTargetRowCount, intColumnCount).Value = _
Sheets("sheet1").Range("A1").Cells(intRowCount, intColumnCount).Value
Next intColumnCount

intTargetRowCount = intTargetRowCount + 1

End If

Next intRowCount

End Sub




I hope this resolves your question. If it has, please mark this question as resolved.

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.
Regards,

Mark
Microsoft Office Specialist Trainer

RE: Excel VBA

Works perfectly.
Many Thanks.

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

VBA tip:

Empty The Clipboard with CutCopyMode

After each Copy/Paste operation in VBA, you should use the following line of code to empty the clipboard. This ensures that the computer memory doesn't overload:

ActiveSheet.Paste
Application.CutCopyMode = False

View all VBA hints and tips


Server loaded in 0.08 secs.