Need Macro to Convert Text To Numbers in Multiple Sheets

rsrasc · #1 04-11-2016, 12:48 AM

Hi all,

I have a file with 40 sheets. The first sheet is named "MyData". The other 39 sheets are named "Page 1" thru "Page 39".

The following code works fine for sheet "MyData".

I have searched the Internet to see if I could find information or a macro that will help me do the same for the other 39 sheets but I have not been successful.

Code:

 
Sub Converting_Text_To_Numbers_1()
 
    Range("D6:F385").Select 'specify the range which suits your purpose-same range for all other sheets
 
 
    With Selection
        Selection.NumberFormat = "###000"
        .Value = .Value
    End With
 
End Sub

For example, the following two (2) codes below are working fine for the 39 sheets but I can't get it to work with the above code.

So if someone can help me with this, I will appreciate it.

Code:

Sub Check_NumberUnMerged()
'
' testmac1 Macro
'
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    With ws
        .Range("CB6:CC205").UnMerge
 
    ' and so on
    '
    End With
Next ws
End Sub

Code:

 
Sub ChangeFont()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
    ws.Cells.Font.Name = "Calibri"
    ws.Cells.Font.Size = "10"
Next
End Sub

Thanks!

macropod · #2 04-11-2016, 01:41 AM

Please clarify your requirements. Your thread title suggests you want to 'Convert Text To Numbers', but the body your post indicates only a desire to change the formatting of a range of cells. Converting text to numbers and changing number formatting are two entirely different things - though both can be done in the one macro.

rsrasc · #3 04-11-2016, 04:49 AM

First, my apologies for not clarifying my requirements.

Basically, on my file, "Page 1" thru "Page 39" all the cells from "D6:F385" has the green triangle (which I failed to mentioned) in the upper left corner of the cell. Probably, calling this "converting text to numbers" is not the right thing to do.

By applying the following number formatting, the green triangle in the upper left corner of the cell goes away, which is my intended result.

Code:

Sub Converting_Text_To_Numbers_1()
 
    Range("D6:F385").Select 'specify the range which suits your purpose-same range for all other sheets
 
 
    With Selection
        Selection.NumberFormat = "###000"
        .Value = .Value
    End With
 
End Sub

The current code or macro is only working for the sheet name "MyData".

If you can provide me a macro that will change the text to number and the number format for "Page 1" thru "Page "39", then it will meet my requirement.

Hoping this time I got the message across, and again sorry for the confusion!

Cheers!

macropod · #4 04-11-2016, 04:57 AM

Changing the number format for all sheets is as simple as:

Code:

Sub FormatNumbers()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
  ws.Range("D6:F385").NumberFormat = "###000"
Next ws
End Sub

rsrasc · #5 04-11-2016, 06:21 AM

Great, thanks!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro to convert text to endnote?	Orifacious	Word VBA	27	03-29-2022 02:58 PM
Macro to replace Image, Numbers in Table and in multiple protected word document	balaji	Word VBA	10	03-24-2016 02:11 AM
I need to convert shaded text into highlighted text on about 80 different long documents. VBA macro?	AustinBrister	Word VBA	8	05-28-2015 02:42 PM
Search a cell that contains words and numbers and convert the numbers to metric	Carchee	Excel Programming	36	10-08-2014 03:16 PM
Convert numbers to a specific text string	francis	Excel	1	10-06-2011 01:43 PM