Macro in Excel

[JoergSchmitt]

Good afternoon,

I just tried to write my first macro. I get a lot of spreadsheets with all text in upper case. I want it to be converted into first letter upper case and rest lower case. I managed to write and record the macro (took me much longer than I care to admit). Issue is, it always applies to the exact cell I wrote it in (C2 in my case). How can I edit it in a way that I can click on any cell and execute that macro with the assigned short cut?

Appreciate any feedback

Thanks

[#2]

@JoergSchmitt 

Hi

You recorded an "Absolute Macro" which repeats the same instructions to the same selection where you recorded it.

You need to Re-record the macro as a "RELATIVE" macro by highlighting the command "USE RELATIVE REFERENCE" in the Code group on the Left side of the Developer Tab of the Ribbon.

The macro recorder will record the steps RELATIVE to your starting point & will run the same steps Relative to the selected cell when you run it with a shortcut or any other method.

Hope that helps

Nabil Mourad

[#3]

@JoergSchmitt 

Hi Jörg,

try this:

Sub ConvertToUpper()
Dim rngCell As Range

For Each rngCell In Selection
rngCell.Value = UCase(rngcell.value)
Next rngCell
End Sub


Sub ConvertToLower()
Dim rngCell As Range

For Each rngCell In Selection
rngCell.Value = LCase(rngcell.value)
Next rngCell
End Sub

Best regards

Bernd

A database full of macros

[#4]

Bernd good morning,

Thanks for the quick reply. I will let you know whether I killed my computer...

Joerg

[#5]

@JoergSchmitt 

It sounds like  you want to change to proper case.  Try this.  It also checks for any cells that have formulas (can cause problems).  Select your cells & run the macro.

Sub ProperCase()
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = Application.Proper(Rng.Value)
End If
Next Rng
End Sub