Looper blooper

[Arne Hegefors]

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("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 <> firstAddress And
c.row = segment.row Or c.row = secID.row
End If
End With

 

[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.

 

[Arne Hegefors]

Hello Tom! Thank you very much for your help! However I do not seem to get
your code to work. I get error message saying: "Compile error: Loop without
Do". I really dont understand that since I just pasted your code and I have
the Do statement. Please if you can help me out I would be very grateful!
Thank you again Tom!!


"Tom Ogilvy" skrev:

 

[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