Update Cell Value Based on Match to Outside Workbook

[Bill Reader]

Hi, friends. I have a list of 10K emails in my active workbook, where many duplicates exist. In another workbook I have a list of bad email addresses. I'm looking for a macro that would help me update the Status column in my active workbook with the word "bad" if there is a match:

"If the email address listed in Column A of my active Workbook1 matches the email address listed in Column A of Workbook2, then place "bad" in Column B of Workbook 1."

Can anyone help with this? Thanks!

 

[nuked]

I haven't tested this so you'll have to report back with any errors, and the lines they occur on. Sorry!

Option Explicit

Sub MatchBads()

Dim wbCurrent As Workbook
Dim wbExternal As Workbook
Dim rng As Range
Dim cel As Range
Dim rngResult As Range
Set wbCurrent = ThisWorkbook
Set wbExternal = Workbooks.Open(Filename:="c:\yourPath\goes\here.xlsm")

With wbCurrent.Sheets(1)

Set rng = .Range("A1:A" & Range("A" & .Rows.Count).End(xlUp).Row)

For Each cel In rng

   Set rngResult = (wbExternal.Sheets(1).Range("A:A").Find(cel.Text, LookAt:=xlWhole)) 
    
    If Not rngResult Is Nothing Then
    
        cel.Offset(, 1) = rngResult
        
    End If
    
Next cel

End with

wbExternal.Close False
Set wbExternal = Nothing

End Sub

 

[Bill Reader]

Thank you for your quick reply! In red is where it first error occurs. I don't think I'm listing my external workbook/file correctly. I suspect the other place I entered my external workbook/file (toward the end) is wrong too. Thanks for any suggestions.

Sub MatchBads()
Dim wbCurrent As Workbook
Dim wbExternal As Workbook
Dim rng As Range
Dim cel As Range
Dim rngResult As Range
Set wbCurrent = ThisWorkbook
Set wbExternal = Workbooks.Open(Filename:="c:\users\breader\desktop\test1\NTHClientContacts.xlsx")
With wbCurrent.Sheets(1)
Set rng = .Range("g1:g" & Range("g" & .Rows.Count).End(xlUp).Row)
For Each cel In rng
Set rngResult = (c:\users\breader\desktop\test1\HardBounces.Sheets(1).Range("A:A").Find(rng.Text, LookAt:=xlWhole))

If Not rngResult Is Nothing Then

rng.Offset(, 1) = rngResult

End If

Next cel
End With
c:\users\breader\desktop\test1\HardBounces.Close False
Set wbExternal = Nothing
End Sub