VLOOKUP find value for future reference, not to be copied in cell 1

[MikeCDPQ]

The Vlookup fonction I am using works well on spreadheet.

How can I use the value found by my Vlookup in VB (result will be like: Someone@someISP.com) to remember that value which I do not need copied on any cell, and use it as my recipient in my ELSEIF condition.

This is the script which basically sends an email to Vendor or to Buyer if Vendor address is not found.


'Find which Email belongs to what vendor
j = 1

Do
EmailVendorNo = EmailList.Sheets("list&quot.Range("A" + CStr(j)).Value

If (EmailVendorNo = CurrentVendorNo) Then
Recipient = EmailList.Sheets("list&quot.Range("f" + CStr(j)).Value
Subject = "Aged Report"
ElseIf EmailVendorNo = "" Then
Recipient = "=VLOOKUP(r5c1,'[VendorEmailAddresses(michel's copy).xls]Buyers'!R2C1:R19C2,2,FALSE)"
Subject = "Failed Report, Vendor not found in email list"
End If

j = j + 1

Loop Until EmailVendorNo = CurrentVendorNo Or EmailVendorNo = ""

Thanks for any help.

 

[SkipVought]

Hi,

The VBA should look like this...

Recipient = Application.VLOOKUP([r5c1],Workbooks("VendorEmailAddresses(michel's copy).xls").Worksheets("Buyers").[R2C1:R19C2],2,FALSE)

I'd get rid of that apostrophy. It will bite you eventually!


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[MikeCDPQ]

Thanks SkipVought,

That looks very good. I get my syntax was somewhat lacking. I'll try it when I get back to work tomorrow.

Thanks.

Michel

 

[MikeCDPQ]

Hi Skip,

We're almost there. I used the script you gave me and renamed my sheet to get rid of apostrophy:

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

Now I am getting a "TYPE MISMATCH" error. Column A on both the active sheet and the Email sheet is formatted as GENERAL.

Guess I'm missing only one little piece of the puzzle ...

Thanks for any suggestions once again.

P.S. tried adding a ".Value" at the end of the code and then I end up with "OBJECT REQUIRED" error message

 

[SkipVought]

What is EmailBuyerNo defined as?
What does the data in Column B look like?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[MikeCDPQ]

EmailBuyerNo is as variant

Column B = hyperlink exmailto: email@address.com)

 

[SkipVought]

Where is [r5c1]? ie workbook and sheet
What is the value?
What row in Workbooks("VendorEmailAddressesTesting.xls&quot.Worksheets("Buyers&quot.[R2C1:R19C2] do you expect to find [r5c1]?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[MikeCDPQ]

[R5C1] is on active sheet at the time of code execution.
Value = 106 at this point in time of the file creation looping.

Expect system to retrieve column B (email@address.com)
where value in column A = 106

At this point, the code looks like (copied from macro code):
EmailBuyerNo = "=VLOOKUP(R5C1,[VendorEmailAddressesTesting.xls]Buyers!R1C1:R30C2,2,FALSE)"

Getting error message:

Unable to send mail, no match found in name & address book(s)

When using code as you sent me yesterday:
WorksheetFunction.VLookup(R5C1, Workbooks("VendorEmailAddressesTesting.xls&quot.Worksheets("Buyers&quot.[R1C1:R19C2], 2)

Getting error message:
Unable to get the Vlookup property of the Worksheetfunction class

I tried defining EmailBuyerNo as string, as variant nothing works.

Just don't know what to do anymore.

Thank for your help once again.

 

[SkipVought]

you're missing the brackets on the lookup value [R5C1] that defines it as a range. Should be...

WorksheetFunction.VLookup([R5C1], Workbooks("VendorEmailAddressesTesting.xls").Worksheets("Buyers").[R1C1:R19C2], 2)

BTW, these are identical statements...

[A1]
Range("A1")
[R1C1]
Range("R1C1")
Cells(1, 1)
Cells(1, "A")

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[MikeCDPQ]

I do have the brackets (was just missing them in last thread, sorry) and it still does not work.

Application.worksheetfunction.vlookup creates error message:

Unable to get the Vlookup property of the Worksheetfunction class

what next ??

Been working on this for 3 days getting ready to give up.
Should count my blessing that I have a conciliant boss )

 

[SkipVought]

Hi,

I NEVER use R1C1 notation -- MUCH too bulky. I incorrectly assumed that it was equivalent -- it is NOT. I am terrable sorry.

Try this...

With Workbooks("VendorEmailAddressesTesting.xls").Worksheets("Buyers")
    x = WorksheetFunction.VLookup(Cells(5, 1).Value, .Range(.Cells(1, 1), .Cells(19, 2)), 2, False)
End With

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[MikeCDPQ]

Skip,


HOURRRRAAAAAA !!!!!

YOU ARE A LIFE SAVER !!!

IT'S WORKING BEAUTIFULLY !!!!

Thank you ever so much !

I owe you big time )

Sincerely,

Michel