Vlookup Referencing a Dynamic Workbook Returns Error 438

[reberryjr]

I'm trying to get a VLOOKUP to work, but I keep getting a 438 error code. I have other VLOOKUPs that work just fine, but they aren't referencing a dynamic named workbook. I've mimiced what I've seen online, and I'm still getting the error.

Sub AddMgrName()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim m As Workbook, s As Workbook
Dim mS As Worksheet
Dim mSLR As Long

Set m = ThisWorkbook
Set mS = m.Sheets("SLA")

mSLR = mS.Range("A" & Rows.Count).End(xlUp).Row

With Application.FileDialog(3)
    .AllowMultiSelect = False
    If .Show Then
        fullpath = .SelectedItems.Item(1)
        Set s = Workbooks.Open(fullpath)
    End If
    
    If s Is Nothing Then Exit Sub

End With

mS.Range("U2:U" & mSLR).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-17]," & "'[" & s & "]SBS'!C[-15]:C[-10],6,FALSE),"""")"

'mS.Range("U2:U" & mSLR).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-17]," & "'[" & s & "]SBS'!C6:C11,6,FALSE),""Review"")"
    
'Macro Recorder Gave Me This            "=IFERROR(VLOOKUP(RC[-17],'[SBS Contact List 2023.05.24 v1.xlsx]SBS'!C6:C11,6,FALSE),""Review"")"

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[Peter_SSs]

Try adding the red text in this line

mS.Range("U2:U" & mSLR).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-17]," & "'[" & s.Name & "]SBS'!C[-15]:C[-10],6,FALSE),"""")"

 

[reberryjr]

Try adding the red text in this line

mS.Range("U2:U" & mSLR).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-17]," & "'[" & s.Name & "]SBS'!C[-15]:C[-10],6,FALSE),"""")"

@Peter_SSs this works like a charm. If you would, help me understand what the .Name addition actually does

 

[Peter_SSs]

@Peter_SSs this works like a charm.

Good news! Thanks for letting us know.

If you would, help me understand what the .Name addition actually does

It puts the name of the other workbook in the square brackets, just as you would if you were entering the formula manually.

 

[reberryjr]

Good news! Thanks for letting us know.


It puts the name of the other workbook in the square brackets, just as you would if you were entering the formula manually.

Gotcha! I hadn't come across that before. Thank you!

 

[Peter_SSs]

Cheers.