Jun 25 2019 04:33 PM
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
Jun 25 2019 06:05 PM
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
Jun 25 2019 10:54 PM - edited Jun 26 2019 02:55 AM
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
Jun 26 2019 06:51 AM
Bernd good morning,
Thanks for the quick reply. I will let you know whether I killed my computer...
Joerg
Jun 26 2019 07:52 AM
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