Type mismatch with VB Vlookup

[MikeCDPQ]

Please refer to thread707-662164

I have defined recipient as string

Recipient = Application.VLookup([r5c1], Workbooks("VendorEmailAddressesTesting.xls&quot.Worksheets("Buyers&quot.[R1C1:R19C2], 2, False)

When the script gets to that point I get error message: TYPE MISMATCH.

Both [r5c1]and C1 from [R1C1:R19C2]in above function are formatted as GENERAL

The value to be retrieved is an email address in column B

When I modify RECIPIENT to a hard coded email address, it works just fine. It looks like it won't accept a looked up value for some reason

Help !!! what am I doing wrong?

Thanks.

 

[mrmovie]

is one of then #N\A ?

 

[MikeCDPQ]

is one of what #N\A ?

The value that I am looking for will always be found on second sheet (static data)

 

[mrmovie]

is it possible to explicitly convert your data to a string

Recipient = CStr(Application.VLookup([r5c1], Workbooks("VendorEmailAddressesTesting.xls&quot.Worksheets("Buyers&quot.[R1C1:R19C2], 2, False))

 

[mrmovie]

can you pipe the value returned from to the screen or a dummy cell to see what you are actually returning from
Application.VLookup([r5c1], Workbooks("VendorEmailAddressesTesting.xls&quot.Worksheets("Buyers&quot.[R1C1:R19C2], 2, False)

do IsObject and IsArray work in VBA?

 

[mrmovie]

at a guess the type mismatch is being generate because you have explicitly declared you recipient variable string and vba is unable to implicitly convert the data you are returning into a string.
what happens if you declare your variable as a Variant type~? do you still get the error?

hope this helps you

vonmoyla