"For Each" to run only once per match

KentKHI

Active Member
Joined
Oct 1, 2004
Messages
492
I am using For Each code to go through a column of email addresses. I only want it to operate once per each email address. The problem is that there are repeats of the same email address in that column, so it will activate the code multiple times for the same email address. Is there a fix for this?

Here is the code snippet:
Code:
       For Each Rng1 In ActiveWorkbook.Sheets("Email").Range("emails")
        If Rng1.Value <> "" Then

Thankyou :)
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

brian.wethington

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
I am not sure what you are doing with this snippet but this will check each cell in the range you have named "emails" and if it is empty it will go to the next cell if it is not it will enter execute the statements within the if statement and exit the for statement. Hope this sort of explains what you are looking for.

Code:
For Each Rng1 In ActiveWorkbook.Sheets("Email").Range("emails")
    If Len(Rng1.Value) <> 0 Then
        'Do whatever you need to do
        Exit For
    End if
Next Rng1
 

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi brian,

Unfortunately, this requires switching from For...Each to a For...Next loop, but I believe it does what you want:

Dim i As Long
With ActiveWorkbook.Sheets("Email").Range("emails")
For i = 1 To .Cells.Count
If WorksheetFunction.CountIf(Range(.Cells(1), .Cells(i)), .Cells(i)) > 1 AND .Cells(i).Value <> "" Then
'Do whatever you need to do
End If
Next i
End With

This simply counts the number of occurences of the current cell's value in the range previously processed, and if greater than 1 occurrence skips it.

Damon
 

brian.wethington

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
Just reread post and realized that what you were getting at is what the OP was probably looking for. Any email in the emails range will end the loop. With the small snippet of code I just made the wrong assumptions I think.
 

Forum statistics

Threads
1,181,055
Messages
5,927,862
Members
436,573
Latest member
CMR237

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top