Does anyone know how I could write a macro to select range from A3 to value
"TEAM"?

Maybe...

Option Explicit
Sub testme2()

Dim FirstCell As Range
Dim SecondCell As Range
Dim RngToSearch As Range

With ActiveSheet
Set FirstCell = .Range("a3")
Set RngToSearch = .Range("a3", .Cells(.Rows.Count, "A").End(xlUp))

With RngToSearch
Set SecondCell = .Cells.Find(what:="Team", _
after:=.Cells(1), lookat:=xlWhole, _
searchorder:=xlByRows, searchdirection:=xlNext, _
MatchCase:=False)

If SecondCell Is Nothing Then
'not found
Set SecondCell = FirstCell
End If
End With

.Range(FirstCell, SecondCell).Select
End With

End Sub




Jaan wrote:

Does anyone know how I could write a macro to select range from A3 to value
"TEAM"?

--

Dave Peterson

Try this

Sub test()
Dim rng As Range
With Sheets("Sheet1").Range("A3:A" & Rows.Count)
Set rng = .Find(What:="Team", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Sheets("Sheet1").Range("A3:A" & rng.Row).Select
Else
MsgBox "Nothing found"
End If
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Jaan" wrote in message ...
Does anyone know how I could write a macro to select range from A3 to value
"TEAM"?

Hi

Thanks. This working.



"Dave Peterson" kirjutas:

Maybe...

Option Explicit
Sub testme2()

Dim FirstCell As Range
Dim SecondCell As Range
Dim RngToSearch As Range

With ActiveSheet
Set FirstCell = .Range("a3")
Set RngToSearch = .Range("a3", .Cells(.Rows.Count, "A").End(xlUp))

With RngToSearch
Set SecondCell = .Cells.Find(what:="Team", _
after:=.Cells(1), lookat:=xlWhole, _
searchorder:=xlByRows, searchdirection:=xlNext, _
MatchCase:=False)

If SecondCell Is Nothing Then
'not found
Set SecondCell = FirstCell
End If
End With

.Range(FirstCell, SecondCell).Select
End With

End Sub




Jaan wrote:

Does anyone know how I could write a macro to select range from A3 to value
"TEAM"?

--

Dave Peterson

Hi

and this working
Thanks!

"Ron de Bruin" kirjutas:

Try this

Sub test()
Dim rng As Range
With Sheets("Sheet1").Range("A3:A" & Rows.Count)
Set rng = .Find(What:="Team", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Sheets("Sheet1").Range("A3:A" & rng.Row).Select
Else
MsgBox "Nothing found"
End If
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Jaan" wrote in message ...
Does anyone know how I could write a macro to select range from A3 to value
"TEAM"?