Number-text format

Question

I have a big table, each cells are filled from a macro. But I don't want formula in the cell, I want just the number so I convert each cell like this :

 Sub FormulaToNumber(sheetName As String, sheetRange As String)
    Dim sh As Worksheet, Cell As Range, Plage As Range
    Set sh = Worksheets(sheetName)

        sh.Select

        With sh

        Set Plage = .Range(sheetRange)

        For Each Cell In Plage
            Cell.Value = Format(Cell.Value, "0.00")
            Cell.NumberFormat = "0.00"
        Next

        End With
        Application.CutCopyMode = False

End Sub

It works, each cells is now a number and not formula BUT it's a number in text format, why ? I am trying to use cell.NumberFormat or plage.NumberFormat. But it doesn't work. Excel keep telling me it's in text format. And when I check by right click on each cell and : cell format/Number/Nombre is selectionned.

And if I click on resolv the problem, convert the cell from text to number it works. The cell is now in number format and the text is align at the right in the cell and my summ function works well.

the cells don't start with a ' do they? which tells excel this is text data.. if you click convert it will also convert to number... — xQbert
No, and if I click on a cell, and I go to the edit bar (just over the cells numbers) and I press enter, everything is good, my cell is now a number and the summ cell works. — David Tarum

Gary's Student Gary's Student · Accepted Answer · 2015-03-13T20:03:08

That is because Format() makes Text and changing the NumberFormat after putting in the Text will not convert it to a number.

Sub qwerty()
    Set Plage = Range("A1:A10")
    For Each Cell In Plage
        Cell.Value = Cell.Value
        Cell.NumberFormat = "0.00"
    Next
End Sub

Number-text format

2 Answers