.Find text in coloumn and return row number

[MadeleineB]

First => I'm trying to return the row number of the first instant of the word "Day 1" in a column, looking down the column.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Second => Once I have the row number I will then format the columns C:N in row ? with a top border.<o></o>

I have the second part done (see below), but I'm stuck on the first:

I was looking as using .Find or xlUp with .Address. Anything I try is not working.


'------------------------first---------------------------------------

myDay1row = Worksheets("Business").Range("C1:C106").Find(What:="Day 1", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).


'-----------------------second--------------------------------------

Worksheets("Business").Range("C" & myDay1row & ":N" & myDay1row).Select

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

 

[mikerickson]

With Worksheets("Buisness")
    myDay1row = .Range("C1:C106").Find(What:="Day 1", After:=.Range("C106"), LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row
End With

 

[MadeleineB]

It works a treat. Thanks Mike. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I will now test it out on multiple worksheets.<o></o>

 

[MadeleineB]

Hi Mike,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
It has an error when I try and run it over multipliable sheets. Each sheet lay out is the same they just have data for different teams.

Run-time error '1004': Select method of Range class failed

Sub Format()
Dim myDay1row As String
With Worksheets("Business")
myDay1row = .Range("C1:C106").Find(What:="Day 1", After:=.Range("C106"), LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row
End With
Worksheets("Business").Range("C" & myDay1row & ":N" & myDay1row).Select

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
'////////////////////////////////////
With Worksheets("Test")
myDay1row = .Range("C1:C106").Find(What:="Day 1", After:=.Range("C106"), LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row
End With
Worksheets("Test").Range("C" & myDay1row & ":N" & myDay1row).Select

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
'////////////////////////////////////
End Sub

 

[mikerickson]

If these sheets are in different workbooks, the first With of the below code needs to be more fully qualified.

With Worksheets("Test")
    myDay1Row = 0
    myDay1row = .Range("C1:C106").Find(What:="Day 1", After:=.Range("C106"), LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row
    If myDay1Row = 0 then
        MsgBox "not found"
    Else
        With .Rows(myDay1Row).Range("C1:N1").Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
    End If
End With