Help with VBA loop

[JRidge]

Hi All,

I have this code...

Dim rgFoundCell As Range    
Const strFindMe As String = "Select Seprations"
     
Application.ScreenUpdating = False
With Sheet5
        Set rgFoundCell = .Range("F:F").Find(what:=strFindMe)
        Do Until rgFoundCell Is Nothing
            
        Set rRange = rgFoundCell.Offset(1)
        If rRange.Value = "No" Then..............................Doing things in here

        Set rgFoundCell = .Range("F:F").FindNext        
        Loop
        End With
    
End Sub

The code is working in that it finds the first instance of strFindMe, Does everything i want it to do..

When it loops it then finds the same first instance and not the next..

Any ideas why??

Any Help would be appreciated.

 

[JRidge]

Its OK i found my problem..

Thank you for you Help with the Loop

Regards

JRidge

 

[shg]

Sub JR()
    Const strFindMe As String = "Select Seprations"
    Dim rLook       As Range
    Dim rFind       As Range
    Dim sAddr       As String

    Set rLook = Sheet5.Range("F:F")
    Set rFind = rLook.Find(what:=strFindMe, After:=rLook(rLook.Cells.Count))

    If Not rFind Is Nothing Then
        sAddr = rFind.Address
        
        Do
            With rFind.Offset(1)
                If .Value2 <> "No" Then
                    If VarType(.Value2) = vbDouble Then
                        ' it's a number; do your thing
                        ' ...
                        ' ...
                        ' ...
                    End If
                End If
            End With

            Set rFind = rLook.FindNext(After:=rFind)
        Loop While rFind.Address <> sAddr
    End If
End Sub

 

[shg]

There's no need to check for <>"No" is it's a number:

Sub JR()
    Const strFindMe As String = "Select Seprations"
    Dim rLook       As Range
    Dim rFind       As Range
    Dim sAddr       As String

    Set rLook = Sheet5.Range("F:F")
    Set rFind = rLook.Find(what:=strFindMe, After:=rLook.Cells.Count)

    If Not rFind Is Nothing Then
        sAddr = rFind.Address

        Do
            With rFind.Offset(1)
                If VarType(.Value2) = vbDouble Then
                    ' it's a number; do your thing
                    ' ...
                    ' ...
                    ' ...
                End If
            End With

            Set rFind = rLook.FindNext(After:=rFind)
        Loop While rFind.Address <> sAddr
    End If
End Sub