VLookup with MATCH & Substitute(Address) combo

Occasional Contributor

Hello I'm trying to code a vbaVlookup with a dynamic Column Number Index, trying to use substitute(address) combo to get the column letter which seems to give problems to my code. But when I manually type the column letter in instead of using the combo it works.

 

 

Sub VbaVlookup() 'Ctrl + Shift + Q

    Dim Table As Range

    On Error Resume Next

    Set Table = Application.InputBox(prompt:="Sample", Type:=8)

    Selection.Formula = "=VLOOKUP($A2," & Table.Address(External:=True) & ",MATCH(SUBSTITUTE(ADDRESS(1,COLUMN(),4),""1"","""")$1,$A$1:$G$1,0)" & ",FALSE)"

    'Use Fill Handle

End Sub

 

 

0 Replies