VLOOKUP using input boxes

[Aranoless]

Hello, I'm very much a beginner in VBA coding. What I wanted to do was to make a macro to vlookup a certain column in table1 to be pasted to table2, and to use input boxes so users can specify which column they want to vlookup.

Problem is, the code can't seem to recognize that I want a range of cells from the user and not just one cell for the reference. The table_lookup value also only took in one cell even after selecting a range of cells in the input boxes. I would appreciate any help regarding this.

This is my code.

Sub VLOOKUPtry()
   
    Dim reff As String
    Dim lookuptable As Range
    Dim columnin As Integer
    Dim exactma As Boolean

    Set lookuptable = Application.Selection

    reff = Application.InputBox("What is your reference?")
    lookuptable = Application.InputBox("What is the range of the data?")
    columnin = Application.InputBox("What number is the column in from the left?")
    exactma = Application.InputBox("If exact match, press 0")

    ActiveCell.FormulaR1C1 = "=VLOOKUP(""" & reff & """, """ & lookuptable.Address & """, """ & columin & """, """ & exactma & """)"

End Sub

 

[jasonb75]

The way that you have written it, lookup table is being set to the cell that was selected before you started to run the code, not the range set by the inputbox.
This should fix that part, but there are a couple of other things that you might need to change as well.

In the last line you have used FormulaR1C1, that probably just needs to be Formula.
In vba, a Boolean is false by default so if the user ignores the prompt to press 0 then it will still be exact match.

Sub VLOOKUPtry()
   
    Dim reff As String
    Dim lookuptable As Range
    Dim columnin As Integer
    Dim exactma As Boolean

    reff = Application.InputBox("What is your reference?")
    Set lookuptable = Application.InputBox(Prompt:="What is the range of the data?", Type:=8)
    columnin = Application.InputBox("What number is the column in from the left?")
    exactma = Application.InputBox("If exact match, press 0")

    ActiveCell.FormulaR1C1 = "=VLOOKUP(""" & reff & """, """ & lookuptable.Address & """, """ & columin & """, """ & exactma & """)"

End Sub