Offset in For statment is not progressing

Question

Here is my code. Basically if the cell in the offset column has a value I need this formula filled into the current cell then offset one down to look at the next row. All I am getting is the first cell filled even though the data goes roughly 10 rows long. The cell F2 is not even originally selected as the first line suggest. Not sure where I am going wrong here!

Sub FillF()
    Range("F2").Select

    For i = 1 To 2000
        If Not IsEmpty(ActiveCell.Offset(0, -5)) Then
            ActiveCell.Formula = "=LOOKUP(RC[-2], 'Master Data'!C[-5], 'Master Data'!C[-4])"
            ActiveCell.Offset(1, 0).Select
        Else
            ActiveCell.Offset(1, 0).Select    
        End If
    Next i
End Sub

Please see http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros — GSerg, Jul 16 '16 at 16:25
Dont use Select, use the counter in your loop to move along, no need to select anything — Steven Martin, Jul 16 '16 at 16:27
Even with removing that it is not moving to the next cell to evaluate. It will just apply the formula on whatever cell is engaged prior to hitting run macro. Sorry--it's been a while since I've had to do anything in VBA so I know I must be doing something wrong that's just silly. — Kylee, Jul 16 '16 at 16:35
You also need to reference the cells directly instead of using `ActiveCell`, not just remove the selections... as in `somesheet.Cells(i+1,6)`. Are you sure column A is non-empty? — arcadeprecinct, Jul 16 '16 at 16:42

caiohamamura · Answer 1 · 2016-07-16T16:53:52.607

Get rid of Select as suggested in comments.

Also make sure to reference Cells from the correct Worksheet so if you run the macro while you are in a different worksheet you won't mess the reference. You can select the sheet by its index number:

Sheet1.Range("F2")

Or by its name:

Sheets("Name of Worksheet").Range("F2")

You may rewrite your code like this:

Sub FillF()
    Dim MySheet As Worksheet
    Set MySheet = Sheets("My worksheet name")

    For i = 2 To 2000
        If Not IsEmpty(MySheet.Cells(i, 1)) Then
            MySheet.Cells(i, 6).FormulaR1C1 = "=LOOKUP(RC[-2], 'Master Data'!C[-5], 'Master Data'!C[-4])"
        End If
    Next i
End Sub

Offset in For statment is not progressing

1 Answers1