macro for formatting numbers stored as text

[traxdmb34]

Hi, I'm very new to VBA. I'm trying to get numbers stored as text converted to numbers, while keeping the same number of decimals the value had while stored as text and add a comma to any number that is over a thousand. The closest I could get was changing the number format to general, but that doesn't give me commas, and anything with a .0 comes back as a whole number. I also tried a custom format like #,###.##, but that gives me a decimal point on a whole number. Any tips or tricks would be appreciated!

ws.Range("C4:C" & lastrow, Cells(lngLastColumn)).Select
With Selection
Selection.NumberFormat = "General" 'need to figure how to get floating decimals
.Value = .Value

 

[Joe4]

Welcome to the Board!

Since your various cells will not all be formatted the same (different number of decimals), you cannot do them all at once like that.

Assuming that your lastrow and lngLastColumn calculations are correct, try replacing the block of code you posted with this:

    Dim cell As Range
    Dim ln As Long
    Dim dc As Long
    Dim ft As String
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    For Each cell In ws.Range(Cells(4, 3), Cells(lastrow, lngLastColumn))
'       Check to see if cell is numeric
        If IsNumeric(cell) Then
'           Find length of cell
            ln = Len(cell)
'           Find decimal in cell
            dc = InStr(1, cell, ".")
'           Build number format
            If dc > 0 Then
                ft = "#,##0." & Left("0000000000", ln - dc)
            Else
                ft = "#,##0"
            End If
'           Apply cell format
            cell.NumberFormat = ft
'           Enter cell value
            cell.Value = cell.Value
        End If
    Next cell
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

 

[traxdmb34]

Welcome to the Board!

Since your various cells will not all be formatted the same (different number of decimals), you cannot do them all at once like that.

Assuming that your lastrow and lngLastColumn calculations are correct, try replacing the block of code you posted with this:

    Dim cell As Range
    Dim ln As Long
    Dim dc As Long
    Dim ft As String
   
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
   
    For Each cell In ws.Range(Cells(4, 3), Cells(lastrow, lngLastColumn))
'       Check to see if cell is numeric
        If IsNumeric(cell) Then
'           Find length of cell
            ln = Len(cell)
'           Find decimal in cell
            dc = InStr(1, cell, ".")
'           Build number format
            If dc > 0 Then
                ft = "#,##0." & Left("0000000000", ln - dc)
            Else
                ft = "#,##0"
            End If
'           Apply cell format
            cell.NumberFormat = ft
'           Enter cell value
            cell.Value = cell.Value
        End If
    Next cell
   
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

thanks for the welcome and THANKS for the help! my issue has been resolved. thanks again!

 

[Joe4]

You are welcome.
Glad I was able to help!