Forum Discussion

JoergSchmitt's avatar
JoergSchmitt
Copper Contributor
Jun 25, 2019

Macro in Excel

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

Macros and VBA

4 Replies

  • Berndvbatanker's avatar
    Berndvbatanker
    Iron Contributor
    Jun 26, 2019

    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

    https://vba-tanker.com/

     

    • JoergSchmitt's avatar
      JoergSchmitt
      Copper Contributor
      Jun 26, 2019

      Bernd good morning,

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

      Joerg

      • Rusty Dane's avatar
        Rusty Dane
        Brass Contributor
        Jun 26, 2019

        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

         

         

  • nabilmourad's avatar
    nabilmourad
    MVP
    Jun 26, 2019

    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

Resources