CODEWITHSUNDEEP
excelvba
research guy
research guy

Reputation: 1

Debug a vlookup() with indirect() vba code

I used vlookup() function and indirect(). It works perfect in the worksheet as 

=VLOOKUP(I$1,INDIRECT("'"&$A3&"'!"&"A:B"),2,0).

However, once I put it into vba, it returns an error, saying

Compile error:Expected: list separator or )

My VBA function code is like this:

Function Haha(title As Variant, sht As Variant)

    Haha= VLOOKUP(title,INDIRECT("'"&sht&"'!"&"A:B"),2,0)

End Function

Upvotes: 0

Views: 150

Answers (1)

Rory
Rory

Reputation: 34075

You can't just write formulas in VBA - they are not the same language. You can use this:

Function Haha(title As Variant, sht As Variant)

    Haha= Application.VLOOKUP(title,Sheets(sht).Range("A:B"),2,0)

End Function

Upvotes: 3

Related Questions