"For Each" to run only once per match

[KentKHI]

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:

       For Each Rng1 In ActiveWorkbook.Sheets("Email").Range("emails")
        If Rng1.Value <> "" Then

Thankyou

 

[brian.wethington]

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.

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]

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]

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.