Macro not working in 2007

[Rick]

I had the below macro to change text into Uppercase in Excel 2003 but now in
2007 it doesn't work.

It seems to get stuck on the For Each x.

Can anyone tell me why?

Thanks
Rick


Sub Uppercase()
' Loop to cycle through each cell in the specified range.
For Each x In Range(Selection, Selection.End(xlDown))
' Change the text in the range to uppercase letters.
x.Value = UCase(x.Value)
Next
End Sub

 

[papou]

Hi Rick
What is the error message?
But never mind, obviously the code is correct, so i would think this is
probably due to the missing declaration of x.
In which case amend:
Sub Uppercase()
' Loop to cycle through each cell in the specified range.
Dim x As Range
For Each x In Range(Selection, Selection.End(xlDown))
' Change the text in the range to uppercase letters.
x.Value = UCase(x.Value)
Next x
End Sub

HTH
Cordially
Pascal

 

[Rick]

Thanks that fixed it. It seems strange because the macro used to work as it
was.

 

[papou]

Rick
This is an option from the VBA editor:
Tools, Option
UnTick the second check box from the top
(something to do with compulsory declaration of variables - can't say what
the exact caption is because I have a French version)

HTH
Cordially
Pascal

 

[Gord Dibben]

Rick

Just a head's up.

Are you aware that your macro will wipe out any formulas you may have in the
range?

Maybe a change.............?

Sub Uppercase()
' Loop to cycle through each cell in the specified range.
Dim x As Range
For Each x In Range(Selection, Selection.End(xlDown))
' Change the text in the range to uppercase letters.
If Not x.HasFormula Then
x.Value = UCase(x.Value)
End If
Next x
End Sub


Gord Dibben MS Excel MVP