I have a loop in which I am trying to search for a cell with a ceratin textline ("Sec type"). I want to search the entire spreadsheet but if it it possible to search only some used range that is preffered. If I find the cell I am lokking for I want to check to see that it is not on the same row as some other things. These rows are specified by segment.row and secID.row. My problem is that the loop never stops running and I do not know what is wrong with it. I guess it is the Loop-line that is erronous but I do not know how to fix it. Please help me out if can! Thanks!
With Range("b1:aa500")
Set c = Worksheets("Berakning").Cells.Find("Sec type", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress And c.row = segment.row Or c.row = secID.row
End If
End With
Loop til you drop
yogiberr
Hi
I would probably make the logic a liitle easier to follow by having a boolean value to test (KeepSearch in the below)
KeepSearching = True
FIRSTADDRESS = c.Address
Do
Set c = .FindNext(c)
If ((c.Address = FIRSTADDRESS) Or (c Is Nothing)) Then
KeepSearching = False
ElseIf c.Row = segment.Row Then
KeepSearching = True
ElseIf c.Row = secID.Row Then
KeepSearching = True
Else
KeepSearching = False
End If
Loop While KeepSearching = True
mamrg
Sub SearchText()
'finds the text "sec type" on Sheet 1 and counts the number of occurences when it isn't on the
'same row as the text "segment" or "secID"
Dim Rng As Range
Dim firstAddress As String
Dim result As Range
Dim segment As Range
Dim secID As Range
Count = 0
Set segment = ThisWorkbook.Worksheets("sheet1").Range("A4") 'range contains "segment"
Set secID = ThisWorkbook.Worksheets("sheet1").Range("A5") 'range contains "secID"
Set Rng = ThisWorkbook.Worksheets("sheet1").Range("a1:aa500")
With Rng
Set result = .Find("Sec type", LookIn:=xlValues) 'find text
If Not result Is Nothing Then
firstAddress = result.Address 'get address of first found text
Do
If result.Row <> segment.Row And result.Row <> secID.Row Then 'not on segment/secID rows
Count = Count + 1 'do stuff here
End If
Set result = .FindNext(result)
Loop While Not result Is Nothing And result.Address <> firstAddress
End If
End With
MsgBox Count
End Sub
Romantic_touch
Boris Mueller
Hi
Your problem is the extra conditions in the loop statement. If you have the blue elements only this will stop the loop when it scrolls back to the first item again, the extra conditions make the loop continue through the loop again past the first item again
Loop While Not c Is Nothing And c.Address <> firstAddress And c.row = segment.row Or c.row = secID.row