SOS - my macros dont work in Vista

[joelle]

Dear Experts,
My macro below will clear any quantity in column I but it will leave formula there if any, and it been working fine with all Excel versions before Vista.
Now, with Vista, it clears all qty's in col I and goes over to col H next door and clear all the "$I$4" I have in col H. Thats so outrageous. Why is Vista that different and is there a way to beat that? Many thanks, pls help me out.
----------------------------------------
Sub clear_col_I()
Dim ws As Worksheet, rngToClear As Range

For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case 'worksheetA"
On Error Resume Next

Set rngToClear = ws.Range("i:i").SpecialCells(xlCellTypeConstants, xlNumbers)
On Error GoTo 0
If Not rngToClear Is Nothing Then rngToClear.ClearContents

Case Else
'do nothing with any other sheets
End Select
Next ws
End Sub
------------------------------------

[Norie]

joelle

As far as I know that code should work in all versions of Excel.

I don't have Vista but I did trial it and I didn't have any problems with code.

As to your problem, the first thing I would suggest is to remove the error handling - that might just be hiding a problem.

[joelle]

Norie,

The codes work with all other Excels except Excel 2007 on Vista.
So, the challenge is to find out (and fix) why it causes my macro to clear
the $I$4 in the formula of the adjacent column, col H.

[Bob Phillips]

Works fine in XL2007 on XP, don't have Vista and XL2007 to check IAA

[mdmackillop]

Can you post sample data?

[Paul_Hossler]

Well, couple of things you might try. I have Vista and 2007 at home but can't test it this week, see if any of these make a difference. I would be surprised if they did, but you never know : pray2:


Sub clear_col_I()
Dim ws As Worksheet, rngToClear As Range
For Each ws In ThisWorkbook.Worksheets
Select Case UCase(ws.Name) ' <<< just in case
Case "WORKSHEETA" ' <<< you had a starting single quote in your post

Set rngToClear = Nothing ' <<< should not be needed, but who knows
On Error Resume Next
Set rngToClear = ws.Range("i:i").SpecialCells(xlCellTypeConstants, xlNumbers)
On Error GoTo 0

If Not rngToClear Is Nothing Then rngToClear.ClearContents
Case Else
'do nothing with any other sheets
End Select
Next ws
End Sub


Paul