looping not my strong point

Stephen Byrom
Stephen Byrom used Ask the Experts™
on
I have some vba that loops through a few hundred rows and finds the first instance of a name in column 1, but I then want it to carry on looping until it finds the first instance of a zero value in column 4, while still retaining the name value in column 1.
If I haven't confused you with my grammar, could you please point me in the right direction?
The code is this;
For x = 2 To lr
    If ws1.Cells(x, 1) = Me.cboChildFullName And ws1.Cells(x, 4) = 0 Then
        ws1.Cells(x, 4) = ws2.Range("TimeNow").Value
        ws2.Range("KidsPresent") = ws2.Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count - 1
    End If
Next x

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
Hi,

Maybe
For x = 2 To lr
    if ws1.Cells(x, 1) = Me.cboChildFullName Then bFoundChild = True
    If bFoundChild  And ws1.Cells(x, 4) = 0 Then
        ws1.Cells(x, 4) = ws2.Range("TimeNow").Value
        ws2.Range("KidsPresent") = ws2.Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count - 1
        bFoundChild  = False
    End If
Next x

Open in new window

Regards
Stephen ByromWarehouse/Shipping

Author

Commented:
Nah, still doesn't find the row.
I have attached the workbook (still work in progress) Angie.xlsmwhich may help. it's the "Register" button and the Leave routine.
Software Architect
Commented:
Hi Stephen,
I have written a small VBA on button click, with inline comments.
Basically it searches for a value 10 on col A, and if that succeeds checks if col B value is 120, if not continues search further. If the condition succeeds (for the same row if col a is 10 and col b is 120 it prints value found in col C).
You can replace the correct column names/numbers and values for your use.
Hope it is useful.
Thanks,
Karrtik
Sub Button3_Click()
    Dim retval
    retval = False
    With Worksheets("Sheet1")
        Set c = .Columns("A" & ":" & "A").Find(10, LookIn:=xlValues, lookat:=xlWhole) 'Search for value 10 in column A
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
            If .Range("B" & c.Row).Value = 120 Then 'If found then check value of B and if it is 120
                retval = True
                Exit Do 'If found exit while
            End If
            Set c = .Columns("A" & ":" & "A").FindNext(c)   'If B value did not match 120 then search further for value of A as 10
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
        If retval = True Then
            c.Columns("C").Value = "Value Found"    'For the row found print in column C as Value found
        End If
        
    End With
End Sub

Open in new window

Stephen ByromWarehouse/Shipping

Author

Commented:
Perfect!!
Thanks for your time and expertise
Karrtik IyerSoftware Architect

Commented:
Thanks Stephen, I am glad I was of some help. :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial