CODEWITHSUNDEEP
excelvba
Kylee
Kylee

Reputation: 1

Offset in For statment is not progressing

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

Upvotes: 0

Views: 33

Answers (1)

caiohamamura
caiohamamura

Reputation: 2728

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

Upvotes: 1

Related Questions