CODEWITHSUNDEEP
vbaexcel
Milena
Milena

Reputation: 17

Loop with VBA Vlookup, with dynamic cell value

Sub tabelvul()

Dim nr(2)

For i = 2 To 31
    nr(1) = Range("AA" & i).Value
    nr(2) = Range("AN" & i).Value
    If nr(2) = 0 Then
        ' do nothing
    Else
        Range(nr(1)).Select
        ActiveCell.FormulaR1C1 = _
            "=VLOOKUP(nr(2),Tabel80[[Kolom1]:[Inschrijver]],2)"
    End If
Next i

End Sub

The first value nr(1) has is B4, so i do get the formula of Vlookup in B4, however he is searching for nr(2) and not the corresponding value of nr(2), which is 1, so I want the formula to look for 1 in the table.

Upvotes: 0

Views: 622

Answers (2)

Xabier
Xabier

Reputation: 7735

I'm pretty sure there are other things that could be improved in your code, but you should change:

"=VLOOKUP(nr(2),Tabel80[[Kolom1]:[Inschrijver]],2)"

To this:

"=VLOOKUP(" & nr(2) & ",Tabel80[[Kolom1]:[Inschrijver]],2)"

Well it looks like you are wanting to enter the Vlookup on a range until nr(2) = Range("AN" & i).value = 0, so I don't know why you would use an array at all, if I knew what you were trying with more detail, could probably make it even better, but with what I know, this could do:

Sub tabelvul()
For i = 2 To 31
    If Range("AN" & i).Value = 0 Then
        ' do nothing
    Else
        Range("AA" & i).FormulaR1C1 = _
            "=VLOOKUP(" & Range("AN" & i).Value & ",Tabel80[[Kolom1]:[Inschrijver]],2)"
    End If
Next i
End Sub

Upvotes: 3

Emilio Lucas Ceroleni
Emilio Lucas Ceroleni

Reputation: 1598

Maybe you should change "=VLOOKUP(nr(2),Tabel80[[Kolom1]:[Inschrijver]],2)" into "=VLOOKUP(" & nr(2) & ",Tabel80[[Kolom1]:[Inschrijver]],2)"

Upvotes: 2

Related Questions