CODEWITHSUNDEEP
vbaexcel
M_66
M_66

Reputation: 299

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

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.

Upvotes: 0

Views: 142

Answers (1)

user4039065
user4039065

Reputation:

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.

Upvotes: 1

Related Questions