Use variables in formula inserted by VBA loop

iaindowner

New Member
Joined
Aug 12, 2014
Messages
18
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.

Code:
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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Hello

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

Code:
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

New Member
Joined
Aug 12, 2014
Messages
18
I see. Amended to this but now getting a type mismatch error

Code:
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

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Sheets(o)

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

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Try:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,876
Messages
5,627,418
Members
416,245
Latest member
Xterminat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top