Forum Discussion

Copper Contributor

Jul 20, 2022

Solved

VBAVlookup

Trying to macro Vlookup with a user prompt to manually select range and type columm number, but the code is not working, what am I doing wrong? Sub VbaVlookup() Dim Table As Range Dim Colnum As Int...

excel

Formulas and Functions

Macros and VBA

HansVogelaar

Jul 20, 2022

NRASA0743

Sub VbaVlookup()
    Dim Table As Range
    Dim Colnum As Integer
    On Error Resume Next
    Set Table = Application.InputBox(Prompt:="Sample", Type:=8)
    Colnum = Application.InputBox(promt:="Sample", Type:=1)
    Selection.Formula = "=VLOOKUP(A2," & Table.Address(External:=True) & "," & Colnum & ",FALSE)"
End Sub

HansVogelaar

MVP

Jul 20, 2022

NRASA0743

Table and Colnum are VBA variables. You cannot use them in a cell formula.

And Colnum is a number, not an object, so don't use Set.

Try this:

Sub VbaVlookup()
    Dim Table As Range
    Dim Colnum As Integer
    On Error Resume Next
    Set Table = Application.InputBox(promt:="Sample", Type:=8)
    Colnum = Application.InputBox(promt:="Sample", Type:=1)
    Selection.Value = Application.VLookup(Range("A2").Value, Table, Colnum, False)
End Sub

NRASA0743

Copper Contributor

Jul 20, 2022

HansVogelaar how do I get the final result as a vlookup formula. The code above it will paste the result as value. Tried replacing Selection.Value with Selection Formula didn't work.

HansVogelaar

MVP

Jul 20, 2022

NRASA0743

Sub VbaVlookup()
    Dim Table As Range
    Dim Colnum As Integer
    On Error Resume Next
    Set Table = Application.InputBox(Prompt:="Sample", Type:=8)
    Colnum = Application.InputBox(promt:="Sample", Type:=1)
    Selection.Formula = "=VLOOKUP(A2," & Table.Address(External:=True) & "," & Colnum & ",FALSE)"
End Sub

NRASA0743
Copper Contributor
Jul 21, 2022
Wow thanks yoy are awesome