How to use a variable as one of the values in Excel VBA VLOOKUP

Question

I'm using VBA in Excel and I'm assigning a VLOOKUP as a formula to a cell. It works fine, but I would like to use a variable that refers to the last cell that contains a value in the column.

In the example below, I would the value for $B$269 to change depending on the number of elements in the closed document.

"=VLOOKUP(B2,'Macintosh HD:Users:myself:Documents:[Master_Terms_Users.xlsm]Master_Terms_Users.csv'!$A$1:$B$269,2,FALSE)"

I know I want to use something along the lines of:

Range("B" & Rows.Count).End(xlUp).Address

With that said, I haven't been able to figure out how to incorporate the result, which is something like $B$269 into the VLOOKUP. I know that those formulas return the correct address because I've used it in Debug.Print.

I tried to do something like this:

"=VLOOKUP(B2,'Macintosh HD:Users:myself:Documents:[Master_Terms_Users.xlsm]Master_Terms_Users.csv'!$A$1:"&GetLastRowFunct&",2,FALSE)"

But that didn't work.

Here is my current code:

Sub GetLastRow()
    Debug.Print GetLastRowFunct
End Sub

Function GetLastRowFunct() As String
    Dim openNwb As Workbook
    Const MasterPath = "Macintosh HD:Users:myself:Documents:"
    Dim strNewFileName As String

    strNewFileName = "Master_Terms_Users.xlsm"

    Set openNwb = Workbooks.Open(MasterPath & strNewFileName)

    Dim openNws As Worksheet
    Set openNws = openNwb.Worksheets(1)
    GetLastRowFunct = openNws.Range("B" & Rows.Count).End(xlUp).Address
    openNwb.Close

End Function

Any recommendations would be appreciated.

user4039065 · Accepted Answer · 2015-08-07 21:28:46Z

1

I would rewrite that function to return the entire range address, including worksheet, workbook and path.

Function GetLastRowFunct() As String
    Const MasterPath = "Macintosh HD:Users:myself:Documents:"
    Dim openNwb As Workbook, strNewFileName As String

    strNewFileName = "Master_Terms_Users.xlsm"
    Set openNwb = Workbooks.Open(MasterPath & strNewFileName)

    with openNwb.Worksheets(1)
        GetLastRowFunct = .Range(.cells(1, 1), .cells(rows.count, "B").End(xlUp)).Address(1, 1, external:=true)
    end with
    openNwb.Close

End Function

The formula construction and assignment becomes simpler to deal with.

rng.formula = "=VLOOKUP(B2, " & GetLastRowFunct & ", 2, FALSE)"

tbh, I'm not sure if you have to supply your own square brackets or not on a Mac.

answered Aug 7, 2015 at 21:28

user4039065

Sign up to request clarification or add additional context in comments.

1 Comment

M_66 Over a year ago

Thank you! That worked. I was wondering about the brackets also, but since the formula worked before without specifying them, I assumed it would also work with your solution, and it does.

Collectives™ on Stack Overflow

How to use a variable as one of the values in Excel VBA VLOOKUP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related