cells.find...

  • Thread starter Thread starter cornishbloke
  • Start date Start date
C

cornishbloke

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
 
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
 
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
 
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
 
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
Click to expand...

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
 
Back
Top