Copy cell when a condition is matched - Excel Macro (VBA)


I am looking for some help with a macro that will do the following, however I am not sure where to start with this as it was not as straight forward as I had thought.

I have the attached worksheet - it has two tabs one called data (with two columns of email addresses on) and another called emails which has a list of email addresses and notes associated with the email address.

I am looking for the marco to:

If any email address in column 'f' or 'h' on the data tab matches an email address listed in column 'a' on the emails tab then where the match was found it should copy the note in column B of the email address matched from the emails tab to column 'z' on the data tab on the row where the match was found.

The bit which I really was not sure about was where two email addresses in column 'f' and 'h' on the data tab on the same row matched two email addresses in column A on the emails tab - which note from column 'b' would it copy to column 'z'? In this instance I would like the note from the email matched in column  'h' to be copied! I am not sure how to handle this either.

Any help would be appreciated.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
"I have the attached worksheet..."

Sorry, nothing is attached.

Presumably, the workbook from your previous question:

[ ]
[ ]
gisvpnAuthor Commented:
Hi- sorry I had not attached - hopefully it is attached below - this workbooks is slightly different they are not identical, nor is the aim of the marco in terms of the previous question.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please find the attached workbook and click on the button to get the desired output.

Sub GetEmailNotes()
Dim dict As Object
Dim sws As Worksheet, dws As Worksheet
Dim cell As Range
Dim i As Long, lr As Long

Application.ScreenUpdating = False

Set sws = Sheets("Emails")
Set dws = Sheets("Data")
lr = dws.Cells(Rows.Count, "F").End(xlUp).Row
Set dict = CreateObject("Scripting.Dictionary")

For Each cell In sws.Range("A2", sws.Range("A" & Rows.Count).End(xlUp))
    If Not dict.exists(cell.Value) Then
        dict.Add cell.Value, cell.Offset(0, 1).Value
    End If
Next cell

For i = lr To 2 Step -1
    If dict.exists(dws.Cells(i, "H").Value) Then
        dws.Cells(i, "Z") = dict.Item(dws.Cells(i, "H").Value)
    ElseIf dict.exists(dws.Cells(i, "F").Value) Then
        dws.Cells(i, "Z") = dict.Item(dws.Cells(i, "F").Value)
    End If
Next i

Application.ScreenUpdating = True
MsgBox "Email Notes have been added successfully.", vbExclamation, "Done!"
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gisvpnAuthor Commented:
Sktneer - spot on as always - and a small amount of code too, neat.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Thanks gisvpn! Glad you found it useful. :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.