Convert numbers into hundreds, thousands & millions

[Expert Opinion]

Hi,
Need your help in code which will convert the numbers into Hundreds, thousands and millions upon selecting option in sheet1. Formatting or coding should be applicable in all the sheets (upcoming new sheets too) and only numbers to be converted and not dates. I have attached a sample file for your review where format is not same for two sheets and same will be the case for new worksheets too in case added.

Please help me on this.



Thanks

 

[Expert Opinion]

Where the results should be.

On the same columns...so we have sheet1 where we will add radio button or drop down with hundred, thousands,millions written.

Basis selection all the worksheets except sheet1 will change the formatting into hundreds, thousands etc.

For example, 100,000 to be shown as 100 when I select thousands!

Once I click original, it should show actuals what we had.

 

[Joe4]

Hi Joe,

This works to an extent for one of the worksheets. Only change will be in formula will be Cell.Value/Mult. Also can we add any code for changing the same to original number post changing it to hundreds or thousands. - Please guide.

Also this covers only one of the worksheets but I want to apply it to all worksheets (20 worksheets - where numbers are there) except main worksheet. Is this possible please?

Please suggest.

I am not sure I understand your first sentence. Can you explain in more detail, maybe show us some before/after images?

There is no problem looping through multiple sheets. What is the name of the sheet you want to exclude from this?

 

[Expert Opinion]

I am not sure I understand your first sentence. Can you explain in more detail, maybe show us some before/after images?

There is no problem looping through multiple sheets. What is the name of the sheet you want to exclude from this?

Hi Joe,

First sentence is addressed now. Only issue is with looping multiple worksheets. Exclusion sheet name is "Sheet1" in our example.

 

[Joe4]

Try this then:

Sub MyFormat()

    Dim wsF As Worksheet, wsD As Worksheet, ws As Worksheet
    Dim factor As String
    Dim mult As Long
    Dim rng As Range
    Dim cell As Range
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
'   Set worksheet where factor is indicated
    Set wsF = Sheets("Sheet1")

'   Get factor from first Sheet
    factor = wsF.Range("A1")
    
'   Determine multiplier
    Select Case factor
        Case "Hundreds"
            mult = 100
        Case "Thousands"
            mult = 1000
        Case "Millions"
            mult = 1000000
        Case Else
            MsgBox "No valid multiplier selected", vbOKOnly, "ERROR!"
            Exit Sub
    End Select

'   Loop through all sheets
    For Each ws In ActiveWorkbook.Worksheets
'       Capture worksheet
        Set wsD = ws
'       Only continue Exit if name is not same as factor sheet
        If wsF.Name <> wsD.Name Then
'           Go to data sheet and set data range
            wsD.Activate
            Set rng = wsD.UsedRange
'           Loop through range and update numbers
            For Each cell In rng
                If IsNumeric(cell) And Len(cell) > 0 Then cell.Value = cell.Value * mult
            Next cell
        End If
    Next ws
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete", vbOKOnly

End Sub

 

[Expert Opinion]

Try this then:

Sub MyFormat()

    Dim wsF As Worksheet, wsD As Worksheet, ws As Worksheet
    Dim factor As String
    Dim mult As Long
    Dim rng As Range
    Dim cell As Range
   
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
   
'   Set worksheet where factor is indicated
    Set wsF = Sheets("Sheet1")

'   Get factor from first Sheet
    factor = wsF.Range("A1")
   
'   Determine multiplier
    Select Case factor
        Case "Hundreds"
            mult = 100
        Case "Thousands"
            mult = 1000
        Case "Millions"
            mult = 1000000
        Case Else
            MsgBox "No valid multiplier selected", vbOKOnly, "ERROR!"
            Exit Sub
    End Select

'   Loop through all sheets
    For Each ws In ActiveWorkbook.Worksheets
'       Capture worksheet
        Set wsD = ws
'       Only continue Exit if name is not same as factor sheet
        If wsF.Name <> wsD.Name Then
'           Go to data sheet and set data range
            wsD.Activate
            Set rng = wsD.UsedRange
'           Loop through range and update numbers
            For Each cell In rng
                If IsNumeric(cell) And Len(cell) > 0 Then cell.Value = cell.Value * mult
            Next cell
        End If
    Next ws
   
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
   
    MsgBox "Macro complete", vbOKOnly

End Sub

Hi Joe,

Can we exclude few rows from used range where formula is captured in rows. We need to excluded those rows.

Any suggestions here?

Thanks in advance,

 

[Joe4]

Hi Joe,

Can we exclude few rows from used range where formula is captured in rows. We need to excluded those rows.

Any suggestions here?

Thanks in advance,

I am not quite clear what you are asking.
Are you saying that we should exclude any cell that has a formula in it (as opposed to a hard-coded value)?

 

[Expert Opinion]

I am not quite clear what you are asking.
Are you saying that we should exclude any cell that has a formula in it (as opposed to a hard-coded value)?

Yes exactly. Currently what is happening is it directly multiplies with multiplier and harde coded value is pasted. We need to keep cell formula intact when this code is made

 

[Joe4]

To exclude cells with formulas, try changing this row:

                If IsNumeric(cell) And Len(cell) > 0 Then cell.Value = cell.Value * mult

to this:

                If IsNumeric(cell) And (Len(cell) > 0) And Not (cell.HasFormula) Then cell.Value = cell.Value * mult

 

[Expert Opinion]

To exclude cells with formulas, try changing this row:

                If IsNumeric(cell) And Len(cell) > 0 Then cell.Value = cell.Value * mult

to this:

                If IsNumeric(cell) And (Len(cell) > 0) And Not (cell.HasFormula) Then cell.Value = cell.Value * mult

Wow, that's awesome. Honestly, this works for me.
I am not sure if I can ask this here, but do you have any suggestions on how I or people like me can improve their VBA skills? Although there is a long way to go, your suggestions are very important.

 

[Joe4]

Wow, that's awesome. Honestly, this works for me.
I am not sure if I can ask this here, but do you have any suggestions on how I or people like me can improve their VBA skills? Although there is a long way to go, your suggestions are very important.

Sure thing!

There is an awful lot to VBA. The way I got started was I picked up a good introductory book (about 20 years ago), and worked through that. That familiarizes you with a lot of the basics and ins and outs of VBA. You can also find lots of articles, tutorials, and videos on line, but those tend to be more issue specific and less general (so a good second step after you do the first). And scanning through other questions people ask here and reading the replies is a great way to learn to.

MrExcel publishing has written lots of different books. Here is one on VBA that I found with a quick scan: Microsoft Excel VBA and Macros (Office 2021 and Microsoft 365)