CODEWITHSUNDEEP
vbaexcelif-statement
GCoxxx
GCoxxx

Reputation: 75

Formatting Cells with Numeric values as Numbers

a very trivial question for you illuminated lot. I am basically trying to identify each cell in a range which has a numeric value, and then format it as 'number' (hence overlooking those cells which contain a string). I have found an excel formula which uses an IF and TRUE/FALSE expression to figure out which cell match the condition, but when running the code in VBA I cannot seem to store the IF statement?

It is probably very silly, as I am new to VBA, but would appreciate all the help!

Code below:

Sub formatnumbers()

Dim rng As Range
Dim cell As Range

Set rng = ActiveSheet.Range("A1:N10")

For Each cell In rng
 cell.Select
         If cell.Formula = "=COUNT(FIND({0,1,2,3,4,5,6,7,8,9},cell))>0, TRUE, FALSE)" = True Then
            cell.NumberFormat = "0.00"
         End If

Next cell

End Sub

Upvotes: 1

Views: 588

Answers (2)

sipi09
sipi09

Reputation: 547

If your goal is to format each cells in a range to numeric, than the easiest way is to multiply the cells with 1.

'329' * 1 = 329
 329  * 1 = 329

So, if you loop over all the cells in the range and simply multiply by 1 you will get numeric values for each cells in the range.

Upvotes: 0

Terminader
Terminader

Reputation: 70

Try this code:

Sub formatnumbers()
    Dim rng As Range
    Set rng = ActiveSheet.Range("A1:G15")
    rng.NumberFormat = "0.00"
End Sub

or:

Sub formatnumbers()
    Dim rng As Range
    Dim cell As Range
    Set rng = ActiveSheet.Range("A1:G15")
    For Each cell In rng
        If IsNumeric(cell.Value) Then
            cell.NumberFormat = "0.000"
        End If
    Next cell
End Sub

Upvotes: 3

Related Questions