PDA

View Full Version : VBA Find question



Papayamoo
10-06-2011, 02:28 PM
Hi, hope this is a quick question but I'm a VBA newbie so here goes.

Trying to format certain cells in my worksheet base the value inside. However, not all of the values I'm looking for is in every worksheet I need to format. So for example, if I'm trying to format cells with the word "banana" but some sheets doesn't have "banana" then it kills the macro and stops looking for the next value.

I thought that I could build an if statement around it but it's not working out too well. What I'm using is Cells.Find(What:="banana"....).Activate. Is this workable or do I need to do something totally different? Any help is greatly appreciated.

M.Yu

Bob Phillips
10-06-2011, 03:20 PM
Do something like



On Error Resume Next
Set cell = Cells.Find(What:="banana"....)
On Error Goto 0
If Not cell Is Nothing

'do stuff
End If

CatDaddy
10-06-2011, 04:25 PM
Sub bananaChecker()
Dim sh As Worksheet
Dim cell As Range
For Each sh In ActiveWorkbook.Worksheets
For Each cell In sh.UsedRange

If cell.Text = "Bananas" Then
cell.Font.Bold = True
End If
Next cell
Next sh

End Sub

Aflatoon
10-07-2011, 01:09 AM
Do something like



On Error Resume Next
Set cell = Cells.Find(What:="banana"....)
On Error Goto 0
If Not cell Is Nothing

'do stuff
End If

May I ask why you have the error handler round the Find?

GTO
10-07-2011, 10:17 PM
May I ask why you have the error handler round the Find?

Howdy Aflatoon,

Cuz the blue lettering looks pretty? Kidding of course and not to answer for Bob, but I do the same thing most of the time, out of habit from setting a ref to a (maybe exists, maybe not) sheet or wb.

Mark