cells.find...
 

Having used the following how do I amend it so that if the search fail
to find the name in column B of Sheet2 it goes on to check column B o
Sheet3? Also how do I prevent it from crashing when the entered name i
not found at all??

Sub FIND_NAME()

Dim MyName As String
MyName = ActiveSheet.Range("A1").Value
Sheets("Sheet2").Activate
Sheets("Sheet2").Cells.Find(What:=MyName, _
LookAt:=xlPart, MatchCase:=False).Activate

End Sub

Any help/pointers would be greatly appreciated

--
Message posted from http://www.ExcelForum.com

cells.find...
 

try this to avoid the nono stopping cycle

Dim MyName As RANGE
MyName = ActiveSheet.Range("A1").Value

For Each cell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)

'checks to the last USED cell


If Not MyName Is Nothing Then

Sheets("Sheet2").Activate
Sheets("Sheet2").Cells.Find(What:=MyName, _
LookAt:=xlPart, MatchCase:=False).Activate

'do whatever it is you want to do.....

End If

Next cell


also maybe you can modify it so instead of looking in Sheet2 only, you
may look in allaof them, but not sure if that's what you want


---
Message posted from http://www.ExcelForum.com/

cells.find...
 

try this to avoid the nono stopping cycle

Dim MyName As RANGE
MyName = ActiveSheet.Range("A1").Value

For Each cell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)

'checks to the last USED cell


If Not MyName Is Nothing Then

Sheets("Sheet2").Activate
Sheets("Sheet2").Cells.Find(What:=MyName, _
LookAt:=xlPart, MatchCase:=False).Activate

'do whatever it is you want to do.....

End If

Next cell


also maybe you can modify it so instead of looking in Sheet2 only, you
may look in allaof them, but not sure if that's what you want


---
Message posted from http://www.ExcelForum.com/

cells.find...
 

try this to avoid the non stopping cycle

Dim MyName As RANGE
MyName = ActiveSheet.Range("A1").Value

For Each cell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)

'checks to the last USED cell


If Not MyName Is Nothing Then

Sheets("Sheet2").Activate
Sheets("Sheet2").Cells.Find(What:=MyName, _
LookAt:=xlPart, MatchCase:=False).Activate

'do whatever it is you want to do.....

End If

Next cell


also maybe you can modify it so instead of looking in Sheet2 only, you
may look in allaof them, but not sure if that's what you want


---
Message posted from http://www.ExcelForum.com/

cells.find...
 

also maybe you can modify it so instead of looking in Sheet2 only, yo
may look in allaof them, but not sure if that's what you want

I do want it to search 2 sheets at once if this is possible. I am onl
a novice at vb though so I'm not sure how to go about this

--
Message posted from http://www.ExcelForum.com