Aug 30 2022 06:00 PM
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