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