Help with Type Mismatch error please

[shhhhh]

Could someone please explain to me why it is that after the below code runs I always get a Type Mismatch error once it actually finishes? Also, any idea why it would run substantially faster if the workbook name is a number than a name?

Sub Search_And_Replace_to_Activate()

For Each sht In ActiveWorkbook.Sheets
For Each cell In sht.UsedRange.Cells
cell.Value = Replace(cell.Value, "a=", "=", 1, 1, vbTextCompare)
Next cell
Next sht
End Sub


Thanks


--------------= Posted using GrabIt =----------------
------= Binary Usenet downloading made easy =---------
-= Get GrabIt for free from http://www.shemes.com/ =-

 

[Chip Pearson]

I can't replicate your error. The code runs fine for me. On what line do you
get the Type Mismatch error? The cause of possible error I could think of
would be if you had a chart sheet in the workbook. Also, you would do
yourself well to get in the habit of declaring your variables. It will
prevent many bugs.

Also, any idea why it would run substantially faster if the workbook name
is a number than a name?

That should make no difference at all.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Bob Phillips]

Do you have a chart sheet? Maybe try

Sub Search_And_Replace_to_Activate()

For Each sht In ActiveWorkbook.Worksheets
For Each cell In sht.UsedRange.Cells
cell.Value = Replace(cell.Value, "a=", "=", 1, 1, vbTextCompare)
Next cell
Next sht
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)