Excel VBA - Capitalizing all selected cells in a column with formulas

Question

I used the code from Siddharth Rout on the following thread to capitalize selected columns but ran into a Error '13' MISMATCH when I used it on a column with cells that had formulas in some of the range.

Excel VBA - Capitalizing all selected cells in column on double click

Here is the code that worked on non-formula based column data from the above link:

   Sub ChangeToUpper()
    Dim rng As Range

    '~~> Check if what the user selected is a valid range
    If TypeName(Selection) <> "Range" Then
        MsgBox "Select a range first."
        Exit Sub
    End If

    Set rng = Selection

    rng = WorksheetFunction.Transpose(Split(UCase(Join( _
          WorksheetFunction.Transpose(rng), vbBack)), vbBack))
End Sub

I searched the forums and didn't find specifics related to this. So I googled it and Mr.Excel had this code but still gave the Error '13', when I cleared out of the error message everything was capitalized. Is there a way to eliminate getting this error?

Here is the code from Mr.Excel:

Sub MyUpperCase()

    Application.ScreenUpdating = False

    Dim cell As Range
    For Each cell In Range("$A$1:" & Range("$A$1").SpecialCells(xlLastCell).Address)
        If Len(cell) > 0 Then cell = UCase(cell)
    Next cell

    Application.ScreenUpdating = True

End Sub

Do you need formulas? It sounds like adding a line of code to convert formulas to values is the simplest solution? — Steven Walker, Jul 15 '16 at 16:55
Access used range, copy it all and then paste special values. I can't remember the exact syntax off the top of my head and am on mobile but you'll easily find examples googling. Other solutions that have been posted are preferable though as you can leave your formulas intact — Steven Walker, Jul 15 '16 at 17:35

cyboashu · Answer 1 · 2016-07-15T17:28:26.410

1

Check If Cell has formula and or errors, If yes then ignore.

   Sub MyUpperCase()

    Application.ScreenUpdating = False

    Dim cell As Range
    For Each cell In Range("$A$1:" & Range("$A$1").SpecialCells(xlLastCell).Address)

      '/ Exclude errors
      If Not IsError(cell) Then
        If Len(cell) > 0 And Not cell.HasFormula Then
          cell = UCase(cell)
        End If
     End If
    Next cell

    Application.ScreenUpdating = True
End Sub

edited Jul 15 '16 at 17:28

answered Jul 15 '16 at 16:55

cyboashu

10,196
2
27
46

This change still resulted in Error '13'. Again clearing the error, the whole sheet was then capitalized. It seems to only be on sheets with formulas that this happens. Thanks for the help I will have to find a work around. – SirSteve Jul 15 '16 at 17:24
Do you have error(s) in your cell? if yes, then check them and exclude using `IsError`. I have updated the answer. – cyboashu Jul 15 '16 at 17:40
No, there is no errors. Thanks for the tip though. – SirSteve Jul 18 '16 at 12:02

Excel VBA - Capitalizing all selected cells in a column with formulas

1 Answers1