How to translate: LOOKUP ... WHERE ... IS EQUAL TO .. in Visual Basic

[MikeCDPQ]

Still looking for a VBA script for Excel that will allow me to perform procedure noted in Subject:

This whole procedure splits a gigantic spreadsheet into smaller files and sends off emails to recipient found in Email List.

Here is part of the script:

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 = "Report sent Successfully"
End If

j = j + 1

Loop Until EmailVendorNo = CurrentVendorNo Or EmailVendorNo = ""

So far, works perfectly


Now, If a certain VENDORNO from the active workbook is not found in the Email List spreadsheet, I need to LOOKUP the BUYER'S EMAIL ADDRESS (other sheet, same workbook) WHERE the BUYERNO is equal to BUYERNO on Active Workbook.

This will then in turn send an email to the buyer who is made aware that his/her vendor list is not complete.

Anybody has a suggestion. Please make it simple as I am not very familiar with VB

Thanks,

Mike