Forum Discussion

NRASA0743's avatar
NRASA0743
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 Integer
On Error Resume Next
Set Table = Application.InputBox(promt:="Sample", Type:=8)
Set Colnum = Application.InputBox(promt:="Sample", Type:=1)
Selection.Value = "=VLookup(a2, Table, Colnum, 0)"
Selection = Selection
End Sub
excel
Formulas and Functions
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Jul 21, 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

4 Replies

  • HansVogelaar's avatar
    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's avatar
      NRASA0743
      Copper Contributor
      Jul 21, 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's avatar
        HansVogelaar
        MVP
        Jul 21, 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

Resources