Code runs in XL97 but not 2000?

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

The following code finds the search string in any worksheet of the current
workbook. It works in XL97, but crashes in XL2000 with "Run time error '13':
Type mismatch".

Why is this? When I go to debug it, the editor is opened at this line:

Set sh = ActiveSheet


Dim i As Integer, sh As Worksheet
Dim MyFind As Range
Application.ScreenUpdating = False
Set sh = ActiveSheet
Dim Message, Title, MyValue
Message = "Enter item number"
Title = "Search Item"
MyValue = InputBox(Message, Title, Default)
For i = 1 To Sheets.Count
Sheets(i).Select
Set MyFind = Cells.Find(What:=MyValue)
If Not MyFind Is Nothing Then
Cells.Find(What:=MyValue).Activate
Application.ScreenUpdating = True
Exit Sub
End If
Next i
MsgBox "Item not found, search again"
sh.Activate
Application.ScreenUpdating = True
End Sub
 
Suspect that when the code is run, the ActiveSheet is a chart sheet or
something other than a worksheet.

Try changing

Dim i as Integer, sh as Worksheet

to

Dim i as Integer, sh as Object
 
Back
Top