Dynamic Vlookup Macro

[harshab6]

Hi Experts,

I need VBA coding which could perform dynamic selection instead of range specific


Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'AUC bal '!R2C1:R10000C1,1,0)"

 

[DanteAmor]

Something like this

Sub test1()
    Dim rng As Range, cl As String, sh As String
    On Error Resume Next
    With Application
        Set rng = .InputBox("Select range", "VLOOKUP", Default:=Selection.Address, Type:=8)
        If rng Is Nothing Then Exit Sub
    End With
    cl = rng.Address
    sh = rng.Worksheet.Name
    Range("B2").Formula = "=VLOOKUP(A2," & "'" & sh & "'!" & cl & ",1,0)"
End Sub

 

[harshab6]

Superb it worked thanks for help

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.