How do I run For : Next Until a specific value is identified?

[tudor30]

I have nested a For / Next searching for value = 1. My question is How do I use the For / Next to search column 2 for values of 1 UNTIL the value in Column 2 = 0?

The value of 1 may be listed 1 or more times in between a values of 0 in column 2. The design should list only the 1's that sequentially follow a value 0 until the next value of 0 is identified in column 2.

Example of my code:
Sub FindAll(strECNN)
Dim rStartCell As Range
Dim i As Integer, j As Integer
Dim f As Worksheet, t As Worksheet
Dim b As Long

b = 10

Workbooks(strECNN & "_BOM.xls").Worksheets(strECNN & "_BOM").Activate
Set f = ActiveSheet
Workbooks("ECN Navigator Pro.xls").Worksheets("Alternates-Substitutions").Activate
Set t = ActiveSheet

Set rStartCell = f.Range("B1")

For i = 1 To WorksheetFunction.CountIf(f.Columns(2), "0")
Set rStartCell = f.Columns(2).Find(What:="0", After:=rStartCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If rStartCell <> "" And rStartCell.Offset(0, 12) = rStartCell.Offset(0, 1) Then
t.Range("G" & b) = rStartCell.Offset(0, 0)
t.Range("H" & b) = rStartCell.Offset(0, 8)
t.Range("J" & b) = rStartCell.Offset(0, 1)
t.Range("R" & b) = rStartCell.Offset(0, 2)
t.Range("T" & b) = rStartCell.Offset(0, 3)
t.Range("U" & b) = rStartCell.Offset(0, 4)
t.Range("W" & b) = rStartCell.Offset(0, 5)
t.Range("X" & b) = rStartCell.Offset(0, 6)

b = b + 1
End If

MsgBox "0 found in " & rStartCell.Address

'''Nested For Next to identify value = 1 in column 2

For j = 1 To WorksheetFunction.CountIf(f.Columns(2), "1")
Set rStartCell = f.Columns(2).Find(What:="1", After:=rStartCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If rStartCell <> "" And rStartCell.Offset(0, 12) = rStartCell.Offset(0, 1) Then
t.Range("G" & b) = rStartCell.Offset(0, 0)
t.Range("H" & b) = rStartCell.Offset(0, 8)
t.Range("J" & b) = rStartCell.Offset(0, 1)
t.Range("R" & b) = rStartCell.Offset(0, 2)
t.Range("T" & b) = rStartCell.Offset(0, 3)
t.Range("U" & b) = rStartCell.Offset(0, 4)
t.Range("W" & b) = rStartCell.Offset(0, 5)
t.Range("X" & b) = rStartCell.Offset(0, 6)

b = b + 1

End If
MsgBox "1 found in " & rStartCell.Address

Next j

Next i

End Sub

Thanks so much for the HELP,
John

 

[SkipVought]

John,

You might want to try something like this

    Set f = Workbooks(strECNN & "_BOM.xls").Worksheets(strECNN & "_BOM")
    Set t = Workbooks("ECN Navigator Pro.xls").Worksheets("Alternates-Substitutions")
    For Each r In Range(f.Cells(1, 2), f.Cells(Cells(f.Cells, 2).End(xlUp), 2))
      select case r.value
        Case 0
          'do stuff
        Case 1
          'do stuff
      end select
    Next

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[tudor30]

Skip,

Thanks for the response.

Are you suggesting I use CASE for the nested For Next?

The desired process works through finding cells in column two with a value of "0" and then adding all following values of "1" in column two UNTIL the value in column two = "0

 

[KenWright]

Just another Option, straight from VBA help (Just search on Loop):-

Do...Loop Statement Example
This example shows how Do...Loop statements can be used. The inner Do...Loop statement loops 10 times, sets the value of the flag to False, and exits prematurely using the Exit Do statement. The outer loop exits immediately upon checking the value of the flag.

Dim Check, Counter
Check = True: Counter = 0 ' Initialize variables.
Do ' Outer loop.
Do While Counter < 20 ' Inner loop.
Counter = Counter + 1 ' Increment Counter.
If Counter = 10 Then ' If condition is True.
Check = False ' Set value of flag to False.
Exit Do ' Exit inner loop.
End If
Loop
Loop Until Check = False ' Exit outer loop immediately.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[SkipVought]

I really didn't take the time to determine what you were trying to accomplish. My point was to suggest a way to Loop thru a range and make some determination. Looks like it could work for you.


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[krinid]

tudor30,
You could use the EXIT FOR command which you can to jump out of a FOR loop partway through.

However, I'm with Ken - use a DO/LOOP. FOR/NEXT is designed for looping through something a fixed number of times. DO/LOOP WHILE/UNTIL is designed to loop until a certain condition is met. Sounds like you want the second.

Alternatively, you can adjust the looping variable in a FOR/NEXT loop to be outside the bounds of the loops, but that's a strange technique.