Forum home » Delegate support and help forum » Microsoft VBA Training and help » Excel VBA
|
|
|
The UK's most regular 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
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel VBA
Resolved · High Priority · Version 2007
Reply Fri 21 Oct 2011, 09:29
Edited on Fri 21 Oct 2011, 09:30Delegate Steve said...
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
For upcoming training course dates see: Pricing & availability
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
Reply Tue 25 Oct 2011, 14:14Delegate Steve said...
RE: Excel VBA
Works perfectly.
Many Thanks.
|