Use variables in formula inserted by VBA loop

[iaindowner]

I have some code which loops through a range of cells. In each cell I want to insert a VLOOKUP formula which will look up a value relative to the active cell in another sheet. Currently I am getting an error "object variable or With Block variable not set".

I am relatively inexperienced in VBA but have been racking my brains for hours. In the code below 'k' is a list box vaule.

I think that the issue may partly be down to the fact that the vlookup formula is using the cell value rather than the cell address.

Sub Retrieve_Click()Dim k As Integer
k = List.Value
Dim off As Range
Set off = Range("D3", Range("D3").End(xlDown)).Offset(0, k * 2)
off.Select


For Each Cell In off.Cells
k = List.Value
Dim ff As Range
ff = Sheets(k).Range("B6:E100")
Dim co As Range
Set co = Cell.Offset(0, -2 * k)
Cell.Value = "=IFERROR(VLOOKUP(" & co & "," & ff & ",4,0),0)"
Next


End Sub

 

[wigi]

Hello

If ff is a Range, you need to use Set:

Sub Retrieve_Click()

    Dim k As Integer
    Dim off As Range
    Dim ff As Range
    Dim co As Range
    
    k = List.Value
    Set off = Range("D3", Range("D3").End(xlDown)).Offset(0, k * 2)
    off.Select


    For Each Cell In off.Cells
        k = List.Value
        Set ff = Sheets(k).Range("B6:E100")
        Set co = Cell.Offset(0, -2 * k)
        Cell.Value = "=IFERROR(VLOOKUP(" & co & "," & ff & ",4,0),0)"
    Next


End Sub

 

[iaindowner]

I see. Amended to this but now getting a type mismatch error

Sub Retrieve_Click()

    Dim k As Integer
    Dim off As Range
    Dim ff As Range
    Dim co As Range
    Dim o As String
    
    
    k = List.Value
    Set off = Range("D3", Range("D3").End(xlDown)).Offset(0, k * 2)




    For Each Cell In off.Cells
        k = List.Value
        o = List.Value
        Set ff = Sheets(o).Range("B6:E100")
        Set co = Cell.Offset(0, -2 * k)
        
        Cell.Value = "=IFERROR(VLOOKUP(" & co & "," & ff & ",4,0),0)"
    Next




End Sub

 

[wigi]

Sheets(o)

Why do you have a String measure there for o ? Why not use k if k is equal to o anyway?

 

[wigi]

Try:

Sub Retrieve_Click()

    Dim k As Integer
    Dim ff As Range
    Dim co As Range


    k = List.Value


    For Each Cell In Range("D3", Range("D3").End(xlDown)).Offset(0, k * 2).Cells
        Set ff = Sheets(k).Range("B6:E100")
        Set co = Cell.Offset(0, -2 * k)
        
        Cell.Value = "=IFERROR(VLOOKUP(" & co & "," & ff & ",4,0),0)"
    Next


End Sub