Vlookup with dynamic range selection using Inputbox

**Pavan Renjal** · 06-12-2013, 02:34 PM

Can anyone help me to perform Vlookup functionality by accepting the range using inputbox. For example, i have 2 columns column A and Column B as mentioned below:

Column A Column B

1 4
2 5
3 1
4 3
5 10
6 9

I want to select the range via inputbox and vlookup the values in Column B with values in Column A or vice versa and find out the difference. Or is there any better way to achieve the same? The following is a simple macro generated with Range hardcoded.

Sub Vlookup_func()
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],R2C[-2]:R6C[-2],1,FALSE)"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C6")
    Range("C2:C6").Select
    End Sub

**Pavan Renjal** · 06-12-2013, 11:41 PM

Can someone help me with the above requirement?

**nilem** · 06-12-2013, 11:48 PM

Hi Pavan Renjal,
Look at the last post in this thread. Maybe you'll find something suitable for you

**Pavan Renjal** · 06-13-2013, 12:01 AM

Hi Nilem,

Can you help me explain this? I am relatively new to macro here. I did a walkthrough but i didnt find anything for me there, i want to use Vlookup functionality.

**:) Sixthsense :)** · 06-13-2013, 12:50 AM

Or

Try like this...

Sub Vlookup_func()
Dim rMyRng As Range, rLkpCell As Range, rRes As Range, iCl As Integer, blType As Boolean

Set rLkpCell = Application.InputBox("Select The Lookup Cell", "Lookup Cell Req", , , , , , 8)
Set rMyRng = Application.InputBox("Select The Vlookup Range", "Vlookup Range Req", , , , , , 8)
iCl = InputBox("Enter the Output Column No", "Result Column # Req")
blType = InputBox("Enter Match Type", "True/False Req", False)
Set rRes = Application.InputBox("Select Result Range", "Result Range Req", , , , , , 8)

rRes.Formula = "=VLookup(" & rLkpCell.Address(0, 0) & "," & rMyRng.Address & _
                "," & iCl & "," & blType & ")"
End Sub