My Search keeps looping

[NessPJ]

Hi,

My search keeps looping. I found some other threads/tips to try and counter that by checking the first found address against the next one, but it does not seem to work.

Can anyone tell me what i am doing wrong please?

    Dim Found As Range
    Dim FoundRegel As Long
    Dim FirstFoundAddr As String
    Dim ActiveFoundCell As Range
    Set SearchRange = Range("A:AZ")
       
    With SearchRange
    Set Found = Cells.Find(What:="AAA", LookIn:=xlFormulas, LookAt:=xlPart)
    If Not Found Is Nothing Then
    FirstFoundAddr = Found.Address
    FoundRegel = Found.Row
    End If
        Do Until Found Is Nothing
            If PlanWb.Sheets(CurSheet).Range("P" & FoundRegel).Value = 1 Then
            PlanWb.Sheets(CurSheet).Range("R" & FoundRegel).Value = 10
            PlanWb.Sheets(CurSheet).Range("S" & FoundRegel).Value = "Checked"
            PlanWb.Sheets(CurSheet).Range("Z" & FoundRegel).Value = "0:15"
            End If
        Set ActiveFoundCell = .FindNext(After:=Found)
            If ActiveFoundCell.Address = FirstFoundAddr Then Exit Do
        Loop
    End With

 

[offthelip]

it is because you never change "Found" after your first Find, so it never changes to nothing, get rid of theActiveFoundCell and replace it with "Found"

 

[NessPJ]

it is because you never change "Found" after your first Find, so it never changes to nothing, get rid of theActiveFoundCell and replace it with "Found"

So i changed the code, but now it stops after the first 'hit' in the search.

    Dim Found As Range
    Dim FoundRegel As Long
    Dim FirstFoundAddr As String
    Dim ActiveFoundCell As Range
    Set SearchRange = Range("A:AZ")
      
    With SearchRange
    Set Found = Cells.Find(What:="AAA", LookIn:=xlFormulas, LookAt:=xlPart)
    If Not Found Is Nothing Then
    FirstFoundAddr = Found.Address
    FoundRegel = Found.Row
    End If
        Do Until Found Is Nothing
            If PlanWb.Sheets(CurSheet).Range("P" & FoundRegel).Value = 1 Then
            PlanWb.Sheets(CurSheet).Range("R" & FoundRegel).Value = 10
            PlanWb.Sheets(CurSheet).Range("S" & FoundRegel).Value = "Checked"
            PlanWb.Sheets(CurSheet).Range("Z" & FoundRegel).Value = "0:15"
            End If
        Set Found = .FindNext(After:=Found)
            If Found.Address = FirstFoundAddr Then Exit Do
        Loop
    End With

 

[NessPJ]

Alright, i fixed the looping error, but now when i moved to a different sheet (with different structure) and adjust the SearchRange it feels like the SearchRange is not actually being used for the Search.
Am i doing this right? Am i using the correct syntax to make the Find function search within the SearchRange?

    Dim Found As Range
    Dim LZVRegel As Long
    Dim FirstFoundAddr As String
    Dim ActiveFoundCell As Range
    Set SearchRange = Range("AP:CD")

    
    With SearchRange
    Set Found = Cells.Find(What:="A1234", LookIn:=xlFormulas, LookAt:=xlPart)
    If Not Found Is Nothing Then
    FirstFoundAddr = Found.Address
    
        Do Until Found Is Nothing
            LZVRegel = Found.Row
            If RittenplanWb.Sheets(CurSheet).Range("BE" & LZVRegel).Value = 1 Then
            RittenplanWb.Sheets(CurSheet).Range("BG" & LZVRegel).Value = 10
            RittenplanWb.Sheets(CurSheet).Range("BH" & LZVRegel).Value = "Test"
            RittenplanWb.Sheets(CurSheet).Range("BO" & LZVRegel).Value = "0:30"
            End If
        Set Found = .FindNext(After:=Found)
            If Found.Address = FirstFoundAddr Then Exit Do
        Loop
    End If
    End With

 

[Alex Blakenburg]

You need to put the period “.” before cells so that it uses the With statement
Set Found = .Cells.Find(What:="A1234", LookIn:=xlFormulas, LookAt:=xlPart)

 

[NessPJ]

You need to put the period “.” before cells so that it uses the With statement
Set Found = .Cells.Find(What:="A1234", LookIn:=xlFormulas, LookAt:=xlPart)

I totally missed that. Thanks for clearing it up!