Macro or Formula to change all characters to uppercase

[IGinc]

Dear Board:

I have several worksheets with a large amount of data.

Worksheets: Contain characters some lower case some uppercase.

Goal of Macro: I would like ALL characters in each entire worksheet to be changed to uppercase.

Is this possible? I am limited to excel formulas at this point. Thanks in advance.

 

[IGinc]

I would like the formula to retain the text in the same cells as well.

 

[Datsmart]

This should do what you are asking.

Sub ConvertToUpperCase()
Dim Rng As Range
    For Each ws In Worksheets
        For Each Rng In ActiveSheet.UsedRange.Cells
            If Rng.HasFormula = False Then 'Ensures Formulas are bypassed
                Rng.Value = UCase(Rng.Value)
            End If
        Next Rng
    Next ws
End Sub

 

[IGinc]

This should do what you are asking.

Sub ConvertToUpperCase()
Dim Rng As Range
    For Each ws In Worksheets
        For Each Rng In ActiveSheet.UsedRange.Cells
            If Rng.HasFormula = False Then 'Ensures Formulas are bypassed
                Rng.Value = UCase(Rng.Value)
            End If
        Next Rng
    Next ws
End Sub

Thank you for your reply.

When running the macro I receive the following error :

'Run Time Error 13'

Type Mismatch

when debugging this is the error/highlighted line:

Rng.Value = UCase(Rng.Value)

Looking forward to a working solution.

 

[Datsmart]

Sorry, I did not test the code on my entire workbook.
Note small change in "UsedRange" line:

Sub ConvertToUpperCase()
Dim Rng As Range
    For Each ws In Worksheets
        For Each Rng In ws.UsedRange.Cells
            If Rng.HasFormula = False Then 'Ensures Formulas are bypassed
                Rng.Value = UCase(Rng.Value)
            End If
        Next Rng
    Next ws
End Sub

This works in my test workbook, but I don't have any unusual sheets, just data, tables, and formulas.

 

[IGinc]

Sorry, I did not test the code on my entire workbook.
Note small change in "UsedRange" line:

Sub ConvertToUpperCase()
Dim Rng As Range
    For Each ws In Worksheets
        For Each Rng In ws.UsedRange.Cells
            If Rng.HasFormula = False Then 'Ensures Formulas are bypassed
                Rng.Value = UCase(Rng.Value)
            End If
        Next Rng
    Next ws
End Sub

This works in my test workbook, but I don't have any unusual sheets, just data, tables, and formulas.

I will try this code out. Could it be that the code does not work, because I explained it wrong. There is only one tab on this particular excel file. I failed to mention that some of my excel files, have just one tab, where others have multiple tabs.

 

[IGinc]

Also the data only has cells, some with different colors, regular data and formulas as well. Most are just data, some have Vlookup, SUM no other macros.

 

[IGinc]

John,

I've run the macro on two other work sheets, with single tabs and it worked. I'm wondering why it wont work on this particular worksheet the only formulas it has is concatante, and . It is a small enough file that I can use upper, thanks for your help, though it would be nice if the macro worked on this file. Thanks again.

Mike

 

[Datsmart]

I have tested the code on a workbook with only one worksheet and it works fine. Even tested against "Merged Cells" that causes errors in some macros, but they change to upper case fine.

Your failing workbook must have something unusual that causes the error.