PDA

View Full Version : Looping Ranges



Aaron71
06-27-2010, 09:01 AM
I am new to VBA and I am using Excel 2007 in Windows 7. I am trying to select a range of 2 rows and 4 columns so that I can "Fill Down" the info from the first row to the second. What I have so far does what I want by inserting a row in the proper place, but I can't figure out how to "Fill down". I have tried using:
Range("A" & i & "D" & (i + 1)) as the object but a get an error that says:
Compile Error:
Expected : list separator or )
How do you do this? BTW, if you want an example of the endpoint you can look at my 2nd attachment on my post "Help with Variables in Ranges". I haven't gotten any more responses so I am asking the question here in a different way.:dunno

Sub Macro1()
'Test Macro for Development
Dim i As Integer
For i = 2 To 20
If cells(i, 2) > 26 Then
cells(i, 3) = "26"
cells((i + 1), 1).EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Else: cells(i, 3) = cells(i, 2)
End If
Next i
End Sub

Bob Phillips
06-27-2010, 10:53 AM
Try this



Sub Macro1()
'Test Macro for Development
Dim LastRow As Long
Dim i As Long
For i = 20 to 2 Step -1
If cells(i, 2) > 26 Then
cells(i, 3) = "26"
Rows(i).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Else
cells(i, 3) = cells(i, 2)
End If
Next i
LastRow = Cells(Rows.Count,"A").End(xlUp).Row
Range("A2:D2").AutoFill Range("A2:D2").Resize(LastRow - 1)
End Sub