why this Vlookup macro with defined variables doesn't work

Question

I recorded the below macro:

Sub Macro2()
'
' Macro1 Macro
'

'
    Range("D108").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],R[-12]C[-1]:R[-4]C[1],2,FALSE)"
    Range("E108").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],R[-12]C[-2]:R[-4]C,3,FALSE)"
    Range("D108").Select
    Selection.AutoFill Destination:=Range("D108:D110"), Type:=xlFillDefault
    Range("D108:D110").Select
    Range("E108").Select
    Selection.AutoFill Destination:=Range("E108:E110"), Type:=xlFillDefault
    Range("E108:E110").Select
End Sub

Then, I tried to modify, so I enter two single numbers as below:



Sub Macro1()
'
' Macro1 Macro
'

Dim row2start As Integer
row2start = InputBox("Write row to start this agenda", " ", "Enter your input text HERE")

Dim des2 As String
des2 = "D" & row2start + 1

Dim des3 As String
des3 = "E" & row2start + 1

Dim newActions As Integer
newActions = InputBox("count how many pending actions", " ", "Enter your input text HERE")
newActions2 = (row2start + 1) + newActions

Dim des4 As String
des4 = des2 & ":D" & newActions2

Dim des5 As String
des5 = des3 & ":E" & newActions2

    Range(des2).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],R[-12]C[-1]:R[-4]C[1],2,FALSE)"
    Range(des3).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],R[-12]C[-2]:R[-4]C,3,FALSE)"
    Range(des2).Select
    Selection.AutoFill Destination:=Range(des4), Type:=xlFillDefault
    Range(des4).Select
    Range(des3).Select
    Selection.AutoFill Destination:=Range(des5), Type:=xlFillDefault
    Range(des5).Select
End Sub

My problem is that I run it and nothing happens. I don't know why? Any ideas why? Technically, it is the same recorded macro, I just updated it with variables. I have double checked the variables but I cannot find any errors. Thanks a lot

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, May 16 '22 at 12:49

score 1 · Answer 1 · answered May 16 '22 at 23:48

I believe the issue with your macro is that your formulae were all using relative addresses, such that the intended look-up range was not being 'looked-up' as the formula was copied down:

Sub Macro1()
' Macro1 Macro Modified
Dim row2start As Long
row2start = InputBox("Write row to start this agenda", " ", "Enter your input text HERE")

Dim des2 As String
des2 = "D" & row2start + 1

Dim des3 As String
des3 = "E" & row2start + 1

Dim newActions As Long, newActions2 As Long
newActions = InputBox("count how many pending actions", " ", "Enter your input text HERE")
newActions2 = (row2start + 1) + newActions

Dim des4 As String
des4 = des2 & ":D" & newActions2

Dim des5 As String
des5 = des3 & ":E" & newActions2

Range(des2, des4).FormulaR1C1 = "=VLOOKUP(RC[-1],R96C3:R104C5,2,FALSE)"
Range(des3, des5).FormulaR1C1 = "=VLOOKUP(RC[-2],R96C3:R104C5,3,FALSE)"
    
End Sub

The code presented above uses absolute addressing for the look-up range, and also avoids using AutoFill, as it is not necessary in this context (where the same formula can be written just as easily to a vertical range of cells, as it can be to a single cell).

why this Vlookup macro with defined variables doesn't work

1 Answers1