Excel or Macro Script: Which way is better to use in Excel?

Asked By anonymous 7060 points N/A Posted on - 04/15/2011

Excel macro or script that can make all data into caps in a excel sheet Some easy way which I can use to make all words CAPS.

Thanks.

 

Mahesh

• Status: Open
• Question Views: 1347
• Answer Count: 3
• Vote Up 4 Vote Down

• Answer Accepted: Yes
• Question Category: MS Excel

Best Answer by Prakashraj

Go To Solution

Best Answer

Best Answer

Answered By Prakashraj 130 points N/A #79191

Excel or Macro Script: Which way is better to use in Excel?

 

Hi,

 

Here is my contribution, I think this is the solution you need:

 

Sub change_caps()

    Dim sh As Worksheet

    Set sh = ActiveSheet

    Dim cll As Range

    For Each sh In ActiveWorkbook.Sheets

        For Each cll In sh.UsedRange

            cll.Value = UCase(cll)

        Next cll

    Next sh

End Sub

 

Regards,

 

Prakash

About Prakashraj

Questions
1

Answers
1

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 04/15/2011
• Question Category: MS Excel

Answered By Sharath Reddy 590495 points N/A #79192

Excel or Macro Script: Which way is better to use in Excel?

 

Good one Prakash. Here is my solution. This can be used in cases where you want all data in all sheets in caps always.

 

It changes as we enter.

 

Public Sub CaseChanger()

 

Dim Cell As Range

Application.EnableEvents = False

For Each Cell In Selection

If Not Cell.HasFormula Then If Not IsNumeric(Cell) And Len(Cell) > 1 Then Cell = Application.Proper(Cell)

Next Cell

Application.EnableEvents = True

 

End Sub

About Sharath Reddy

Questions
1

Answers
14599

Best Answers
2290

• Vote Up 0 Vote Down

• Posted on - 04/15/2011
• Question Category: MS Excel

Answered By fraklin adams 0 points N/A #79193

Excel or Macro Script: Which way is better to use in Excel?

Here's my solution: Sub Uppercase() ' Loop to cycle through each cell in the specified range. For Each x In Range("A1:A5") ' Change the text in the range to uppercase letters. x.Value = UCase(x.value) Next End Sub Correct me if I'm wrong.

Regards,

Fraklin

About fraklin adams

Questions
3

Answers
1

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 04/19/2011
• Question Category: MS Excel