Endless loop

[freddie mac]

I have a loop in which I am trying to search for a cell with a cerati
textline ("Sec type"). I want to search the entire spreadsheet but i
it it possible to search only some used range that is preffered. If
find the cell I am lokking for I want to check to see that it is not o
the same row as some other things. These rows are specified b
segment.row and secID.row. My problem is that the loop never stop
running and I do not know what is wrong with it. I guess it is th
Loop-line that is erronous but I do not know how to fix it. Please hel
me out if can! Thanks!

With Range("b1:aa500")
Set c = Worksheets("Beräkning").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 <> firstAddres
And c.row = segment.row Or c.row = secID.row
End If
End Wit

 

[Tom Ogilvy]

Dim c as Range, rng as Range
Dim firstAddress as String
With Worksheets("Beräkning").Cells
Set c = .Find("Sec type",LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
if c.row = segment.row Or c.row = secID.row then
if rng is nothing then
set rng = c
else
set rng = Union(rng,c)
end if
Set c = .FindNext(c)
Loop While c.Address <> firstAddress
End If
End With
if not rng is nothing then
msgbox "Found at " & rng.Address
rng.Select
else
Msgbox "Doesn't match criteria"
End if

If you want to limit your search to a specific area, change


With Worksheets("Beräkning").Cells

to

With Worksheets("Beräkning").UsedRange

or

With Worksheets("Beräkning").Range("A1:Z22")

as examples.

In my experience, I think FIND only looks at the Usedrange by default.

 

[Tom Ogilvy]

Just missing an end if

Sub efg()
Dim c As Range, rng As Range
Dim firstAddress As String
With Worksheets("Beräkning").Cells
Set c = .Find("Sec type", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
If c.Row = segment.Row Or c.Row = secID.Row Then
If rng Is Nothing Then
Set rng = c
Else
Set rng = Union(rng, c)
End If
End If
Set c = .FindNext(c)
Loop While c.Address <> firstAddress
End If
End With
If Not rng Is Nothing Then
MsgBox "Found at " & rng.Address
rng.Select
Else
MsgBox "Doesn't match criteria"
End If

End Sub