MS Excel 2007 on XP Pro
I cannot get this code to work but cannot see anything wrong with it can
anyone assist?
It runs but ends up on cell B6 presumably not having found the searched for
text.
Any suggestions will be appreciated.

Sub BACK()
'
' BACK Macro
'

'
Sheets("School1").Select
Range("B5:QJ5").Select
Dim varRange As Range
Dim varFound As Variant, varSearch As Variant
varSearch = Range("A3")
Set varRange = ActiveSheet.Range("B5:QJ5")
Set varFound = varRange.Find(varSearch, lookat:=xlWhole)
If Not varFound Is Nothing Then varFound.Activate
ActiveCell.Offset(1, 0).Activate


End Sub

When you use Find in VBA, it uses the last settings that were used in the
previous find -- either by code or by the user.

So if you expect it to be looking for a partial match or ignoring case, it could
be failing because of that.

I'd specify all those parms in the .find() statement and retest.



Wes_A wrote:

MS Excel 2007 on XP Pro
I cannot get this code to work but cannot see anything wrong with it can
anyone assist?
It runs but ends up on cell B6 presumably not having found the searched for
text.
Any suggestions will be appreciated.

Sub BACK()
'
' BACK Macro
'

'
Sheets("School1").Select
Range("B5:QJ5").Select
Dim varRange As Range
Dim varFound As Variant, varSearch As Variant
varSearch = Range("A3")
Set varRange = ActiveSheet.Range("B5:QJ5")
Set varFound = varRange.Find(varSearch, lookat:=xlWhole)
If Not varFound Is Nothing Then varFound.Activate
ActiveCell.Offset(1, 0).Activate


End Sub

--

Dave Peterson

Sub simpler()' However selections are NOT necessary to work with cells.
On Error Resume Next
Sheets("school1").Select

Range("b5:x5").Find(What:=Range("a3"), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False) _
..Offset(1).Activate

End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Wes_A" wrote in message
...

MS Excel 2007 on XP Pro
I cannot get this code to work but cannot see anything wrong with it can
anyone assist?
It runs but ends up on cell B6 presumably not having found the searched
for
text.
Any suggestions will be appreciated.

Sub BACK()
'
' BACK Macro
'

'
Sheets("School1").Select
Range("B5:QJ5").Select
Dim varRange As Range
Dim varFound As Variant, varSearch As Variant
varSearch = Range("A3")
Set varRange = ActiveSheet.Range("B5:QJ5")
Set varFound = varRange.Find(varSearch, lookat:=xlWhole)
If Not varFound Is Nothing Then varFound.Activate
ActiveCell.Offset(1, 0).Activate


End Sub