Dynamic sheet name in excel formula

Hello I’m trying to put a variable inside a write cell.
image

But I’m having error of.

How do I fix this error? or is there any alternative way for the vlookup method?

@acdeleon123,

Can you try with hardcode name of sheet first? if that works then follow the same with dynamic name.

make sure sheet name does not have space.

It works if it’s hardcoded but not when in a variable

@acdeleon123

try

=VLOOKUP(Z2,’"+in_strSheetNameVendor.ToString+"’!$D:$H,5,0)

Had an error of

Read Range: Could not read range .

Hi @acdeleon123

I think the variable you are passing is empty (in_StrSheetNameVendor),put a message box and check if the in_StrSheetNameVendor has value in it.

Thanks

How about share your excel file?

Here’s the file.
Test1.xlsx (316.4 KB)

Forum0615.zip (291.3 KB)
I dont know business model, so I try VLOOKUP formula like this.