.Find over a range of cells not just a row.

[MadeleineB]

I'm trying to search for text over a range of cells. My code works for one row but I can't get it to work for a range of cell AP6:HM55<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Help please?<o></o>
<o></o>
With Worksheets("Buisness")
myDay1row = .Range("9:9").Find(What:="Day 1", After:=.Range("IV9"), LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row
End With<o></o>
<o> </o>
Thanks<o></o>

 

[MickG]

Hi, You may find what you want if you look in the VB Help for "FindNext"
Regards Mick

 

[MadeleineB]

I've look at the VB Help and tryed to put the range up the top like the below but I get a error Run-time eror '1004': Application-defined or object-defined error.

If I take out the After:=.Range("HM55") I then get the error Run-time error '91': Object variable or With block varable not set

My VB knowledge is not good enough to fix it. Can I take away the With statment?

With Worksheets("Buisness").Range("AP6:HM55")
myDay1row = .Find(What:="Day 1", After:=.Range("HM55"), LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row
End With<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[MickG]

Hi Try this , mod the Sheet name and Ranges (The Range is the range to look at) to suit.
I don't know what you want to do with the results , so there in a Msgbox !!

Dim c As Range, fstaddress, oDay As String
With Worksheets("sheet83").Range("a1:h500")
    Set c = .Find("Day 1", LookIn:=xlValues)
    If Not c Is Nothing Then
        fstaddress = c.Address
        Do
        Set c = .FindNext(c)
        oDay = oDay & c.Address & Chr(10)
        Loop While Not c Is Nothing And c.Address <> fstaddress
    End If
End With
MsgBox "Text ""Day 1"" found in Cells :- " & Chr(10) & oDay

Regards Mick