VBA VLOOKUP Formula Syntax Error

[wsnyder]

Hi all,

Using Excel 365.

I'm trying to build a VLookup Formula as a string in VBE so I can apply the formula to a Range using the Formula property of the Range Object.
I created a Function that returns a string
When in Break Mode, I hover on all of the variables, all fo the variables return expected values.

When I run the code, it returns an error:

Run-time error '438':
Object doesn't support this property or method

and highlights my line where I am creating the formula string

I've looked at this for a couple of hours now and can't figure out what is wrong.
Any thoughts?

Thanks
-w

Private Function GetLookupFrmla(WhereFrmlaGoes As Range, _
                                LookupWhat As String, _
                                wb As Workbook, _
                                LookupArray As String, _
                                ReturnColNbr As Long) As String
                                            
    'Purpose    :   Get lookup frmla

    'Objects
        Dim ws As Worksheet
        
    'Initialize objects
        Set ws = wb.Worksheets("Pivot")
    
    'Get lookup frmla
        GetLookupFrmla = "=IFERROR(VLOOKUP(" _
                             & LookupWhat & "," _
                             & "'[" & wb.name & "]" _
                             & ws.Name & "'!" _
                             & LookupArray & "," _
                             & ReturnColNbr & "," _
                             & "FALSE),0)"
                
    'Tidy up
        Set ws = Nothing

End Function

 

[RoryA]

That function looks ok - what's the line that is calling it?

 

[mackc557]

Two things to consider:
1. Do you have the referenced workbook open? From what I've been told, you can't do anything with an unopened workbook.
2. Try using a helper variable for your string, pass it into the variable, then return its value. Just a way to check.