Modifying For Loop

[Dave Birley]

I have a For Loop in the form...

Dim myRowCount, myCount Variant
myRowCount = 0
myCount = 0

For Each myCell In Range("C1:C65536")
Set myCell = myCell(myRowCount + 1, 1)
StartRow = myCell.Row
If myCell.Value <> myCell(2, 1).Value Then
.. 'Monkey business here bumps myCount upwards
..
myRowCount = myCount + 1
myCount = 0
Else
myCount = myCount + 1
End If
Next myCell

I do the monkey-business with an If statement which contains a While
statement which moves the pointer down several Rows. I still need to get back
to the top of the For Loop, but I want to start at the next row in the WS.

Data:
2 Ping 'Starting here
3 Ping
4 Pong 'Next start here
5 Pong
6 Pong
7 Pong
8 Pang 'Next
9 Pang
10 Pang
11 Pung 'Etc.

As in the example, I have no way of knowing in advance how many rows will be
in the set.

The ultimate objective is this: the data set contains key words in a
particular cell. The For Loop is on the Column containing SSNs, and the sets
are contiguous rows containing the same SSN. One of the rows might contain a
certain key word ("PEN") which will identify the set as being "of interest".
However the row with the key word cell in it might be any one of the rows in
the set. To find it and process the rows, I have a While..WEnd loop inside an
If..Else..End If construct (which seems to work Ok). Now I need to repeat the
process starting at the first row after the completed set.

In the form above, it is failing to find the correct row to start the next
cycle.

Anyone got any suggestions (other than I should go back to VFP or get a life
<g>)?

 

[dq]

Dave,

The problem is that you are changing MyCell twice in the loop, once by
the loop itself
For Each myCell In Range("C1:C65536")
and once by using
Set myCell = myCell(myRowCount + 1, 1)
I think if you rewrite your loop like shown below, it should work

DQ

Dim myRowCount, myCount As Variant
myRowCount = 0
myCount = 0

myRowCount = 0
Do While myRowCount <= 65535
myRowCount = myRowCount + 1
Set myCell = Cells(myRowCount, 3)
StartRow = myCell.Row
If myCell.Value <> myCell(2, 1).Value Then
' 'Monkey business here bumps myCount upwards
myRowCount = myCount + 1
myCount = 0
Else
myCount = myCount + 1
End If
Loop

 

[Dave Birley]

Thanks, dq, That appears to be doing the trick. The monkey business needs a
bit of tweaking, but the core problem I was having was with the loop.

You da Man (or da Ma'am <g>)!!!

 

[dq]

Man, last time I checked