Making an "error" message for a macro

[amandarose80]

I have a search box in a spreadsheet that has 26 tabs (A-Z). This is so you can find a name or a client number.
This is the code I used:

Sheets(Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", _
"Q", "R", "S", "T", "U", "V", "W", "X", "Y&quot).Select
Sheets("A&quot.Activate
Sheets("Z&quot.Select Replace:=False
Selection.Find(What:=Sheets("main&quot.Range("c7&quot.Value, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Sheets("Main&quot.Range("c7&quot.ClearContents

End Sub

But if the name is not present, you get a runtime '91' error. How can I make an error message appear instead??

Thanks for the help!!

 

[HobbitK]

amandarose ...
I am a VB programmer, so this may have to be played with a little bit to make it work in Excel specifically, however..
Write an ErrorHandler for it..
I typically put them after all your variables are Dimensioned but Before your FIRST line of code in this module, put something like this ...

On Error GoTo MyErrorTrap

Then all your code here
.
.
.
After the last line of code you have ... put this
Exit sub
My ErrorTrap:
If err.Number = 91 then
MsgBox("Name not found",vbInformation + vbOkOnly "Search not Found&quot
Sheets("Main&quot.Range("A1&quot.Select
err.clear 'Reset error object counter
End Sub

HTH
Michael

 

[rmikesmith]

Amanda,

Michael's code should work fine in VBA with no tweaking.

My first thought was to reply with error handling code, too. However, the generation of an error in your procedure is a consequence of completely expected conditions... namely, the case when a searched for name does not exist. In these situations, I prefer to anticipate and prevent an actual run-time error from occurring, saving the raising of errors for abnormal events (hey, this may be a matter of philosophy ). By the way, I mocked up your workbook setup and used the posted code but couldn't get it to work whether or not the searched for string was present (interestingly, it raises Error 91). So, my example that follows takes a slightly different approach. It relies on the fact that names beginning with "D", for instance, will be located in the "D" worksheet. This also reduces the number of cells to be searched. In any case, the relevant check for a failed search is highlighted in red.

Sub Search()
Dim SearchFor As Variant
Dim MatchRng As Range
Dim Wks As Worksheet

    SearchFor = Sheets("Main").Range("C7").Value
    Set Wks = Worksheets(UCase(Left$(SearchFor, 1)))
    
    Set MatchRng = Wks.Cells.Find(What:=SearchFor, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)

If Not MatchRng Is Nothing Then
      Wks.Activate
      MatchRng.Select
    Else
      MsgBox SearchFor & " was not found", vbExclamation + vbOKOnly, "Search"
    End If

Sheets("Main").Range("c7").ClearContents

End Sub

Note -- You could also check to see whether anything was actually entered in cell C7 before proceeding.

p.s. I omitted the SearchFormat parameter since I'm using Excel 97 and 2000.


Regards,
Mike