PDA

View Full Version : skipping ahead in for loop



CatDaddy
06-20-2012, 07:58 AM
For Each cell In Range("M2:M" & lr)
r = cell.Row
size = 0
If Range("M" & r).Text = Range("M" & r + 1).Text Then
For i = 1 To 100
If Range("M" & r).Text <> Range("M" & r + i).Text Then
size = i
Exit For
End If
Next i
End If
'Do Stuff
cell = cell.Offset(size, 0)
Next cell

basically trying to have only one iteration per unique value in column M

CatDaddy
06-20-2012, 11:14 AM
is it just:
Set cell = cell.offset(size,0)

CatDaddy
06-20-2012, 11:44 AM
nevermind that isnt working

mikerickson
06-20-2012, 08:10 PM
Rather than a logic like'pseudo code

For each Cell in Range
If Cell is duplicate Then
skip ahead
Else
do something
End If
Next
You could use a logic like'pseudo-code

For each Cell in Range
If Cell is first of its value Then
do something
End If
Next as in this routine that counts the values in A2:B10 excluding duplicatesDim cell As Range, myRange As Range
Dim countUniques As Long
Set myRange = Range("A2:B10")

For Each cell In myRange

If Application.CountIf(Range(cell, myRange.Cells(1, 1)), cell.Text) = 1 Then
countUniques = countUniques + 1
End If

Next cell

CatDaddy
06-21-2012, 08:58 AM
ok so let me tell you basically i am running through a long list of emails in column m, and im sending emails line by line, but i am trying to add information from all lines with the same email address to one email instead of a whole bunch to the same address...i dont know if the countif would work for that with any kind of speed, what i ended up doing was just deleting the duplicates beneath the first instance of the email so the next X iterations were just blanks and could be skipped.

what i really wanted to know though is if there is a way to manipulate the counter in the for loop, and why the way i was doing it didnt work

mikerickson
06-21-2012, 12:11 PM
Yes you can maipulate the loop variable inside the loop.
Great care should be taken to insure that that maniputlation shortens the loop rather than lenghthens it.
Here is an example
Dim i As Long
For i = 1 To 10
If MsgBox(i, vbYesNo) = vbNo Then
i = i + 1
End If
Next i

MsgBox i

CatDaddy
06-21-2012, 12:15 PM
can you explain then why setting the cell to a cell further along in the iterations of the for each loop did not work?

CodeNinja
06-21-2012, 12:48 PM
CatDaddy,
I don't think there is a skip like you want, however, the same thing can be accomplished by the following:


dim iCount as integer
dim rng as range
set rng = sheets("Sheet1").range("M2:M" & lr) ' change "Sheet1" to whatever sheet it really is...


For iCount = 1 to rng.cells.count
r = rng.cells(iCount).Row
size = 0
If Range("M" & r).Text = Range("M" & r + 1).Text Then
For i = 1 To 100
If Range("M" & r).Text <> Range("M" & r + i).Text Then
size = i
Exit For
End If
Next i
End If
'Do Stuff
iCount = iCount + size
Next cell

CatDaddy
06-21-2012, 12:50 PM
yeah i suppose i could do it like that...i am curious as to why it wouldnt work the other way though

mikerickson
06-21-2012, 05:11 PM
In the loop
For i = 1 to 10 Step 2
' ...
Next i
Each interation increases i by the Step value (default 1).

In the loop
For each oneCell in SomeRange
' ...
Next oneCell

creates a collection of the cells in SomeRange
each interation of the loop takes each of those in turn. There is no "incrimenting" of oneCell.

Consider a slightly different loop that illustrates how a For Each loop works.
Dim oneString as Variant

For Each oneString in Array("alpha", "beta", "gamma", "delta")
MsgBox oneString
oneString = "cat"
Next oneStringThere isn't a "cat"+1, so its not surprising that you see 4 MsgBoxes.
Changing the code to oneString = "gamma" doesn't change the situation, you still see 4 message boxes.

Analogously, run this loop
For i = 1 To 10
If MsgBox(i, vbYesNo) = vbNo Then Exit Sub
If i > 1 Then i = 100
Next i
Now change 100 to -2