Search code examples
excelvlookupinputbox

Retain the static/locked Range from Input Box for VLOOKUP

I am attempting to use input from InputBox, where the InputBox determines the TableArray for VLOOKUP.

Once the Worksheet and Cells are selected and added to the VLOOKUP Formula, they lose the static path and it makes them dynamic.

Example: 

    Dim PrevBacklog As Range
    Set PrevBacklog = Application.InputBox(prompt:="Select the Worksheet/Tab and cell range for lookup", Title:="Previous Backlog Tab", Type:=8)
    ActiveSheet.Range("I2:I1000").Formula = "=VLOOKUP(A2," & PrevBacklog.Address(0, 0, xlA1, 1) & ",9,False)"

InputBox will capture "Sheet2!$A$1:$N$2", but the resulting formula is 

=VLOOKUP(A2,Sheet2!A1:N2,9,FALSE)

This is problematic as it it relative to each cell. I would like the formula to result in 

=VLOOKUP(A2,Sheet2!$A$1:$N$2,9,FALSE)

How does one retain the locked/static address?

Solution

  • Change 

    PrevBacklog.Address(0, 0, xlA1, 1)

    to

    PrevBacklog.Address(True, True, xlA1, True)

    Or more simply, since most of those are the default values:

    PrevBacklog.Address(External:=True)