Problem with Find (VBA)

[jst3712]

Hi.

I need some VBA code that will Find and then select a cell that has a value of "SALES" (and ignore cells that contain a string containing the word SALES (not whole), like "SALES REPORT").
I recorded a macro that does it ok, BUT, if it detects a cell that contains a string including the word SALES, like "SALES REPORT", it spits out an error message which I don't understand, even if a different cell contains the "SALES": "Run-time error '91': Object variable or With block variable not set"

This is my code -

Cells.Find(What:="SALES", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

Scenario #1 -
Cell A1 = [blank]
Cell A2 = 'SALES' <<< ok/no error!

Scenario #2 -
Cell A1 = 'SALES REPORT' <<< causes the error, despite cell A2 meeting the criteria.
Cell A2 = 'SALES'

What i want to know is:
1) what does this error mean?
2) why does the error appear in scenario #2 and not scenario #1?
3) what can I do to make it work? Ideally I would also like it to bring up a message to notify the user if it found a cell containing "SALES", or if it didn't. Nothing should happen in all other cases. So if you could include this in your solution as well, that would be great.


Thanks in advance!
Jason
Still learning!!!

 

[AlphaFrog]

The error means it couldn't find a match. WHY it couldn't find a match, I'm not sure. Your .Find criteria is looking for an exact match. So if you have a trailing space after SALES in cell A2 or if you have quotes around it, that wouldn't be an exact match.

Try something like this. It shouldn't error, but if it couldn't find a match before, it still wouldn't find a match with this new code.

Sub Find_Sales()
    Dim Found As Range
    Set Found = Cells.Find("SALES", ActiveCell, xlFormulas, xlWhole, xlByRows, xlNext, False)
    If Not Found Is Nothing Then
        Found.Select
        MsgBox " Match found in cell " & Found.Address(0, 0), vbInformation, "Match Found"
    Else
        MsgBox "No match found for ""SALES""", vbCritical, "No Match"
    End If
End Sub

 

[jst3712]

The error means it couldn't find a match. WHY it couldn't find a match, I'm not sure. Your .Find criteria is looking for an exact match. So if you have a trailing space after SALES in cell A2 or if you have quotes around it, that wouldn't be an exact match.

Try something like this. It shouldn't error, but if it couldn't find a match before, it still wouldn't find a match with this new code.

Sub Find_Sales()
    Dim Found As Range
    Set Found = Cells.Find("SALES", ActiveCell, xlFormulas, xlWhole, xlByRows, xlNext, False)
    If Not Found Is Nothing Then
        Found.Select
        MsgBox " Match found in cell " & Found.Address(0, 0), vbInformation, "Match Found"
    Else
        MsgBox "No match found for ""SALES""", vbCritical, "No Match"
    End If
End Sub

Just tried the code and it works beautifully!
The code you added is what I was missing, only I couldn't work out what it wanted!

By the way, somehow I don't think the error message (that appeared) had anything to do with no matches found, but rather the missing code that you filled me in on, i.e. 'Dim Found As Range / Set Found = .... etc'.

But it's working now, that's the main thing. So thank you AlphaFrog!

Jason.


*********** RESOLVED ***********