CODEWITHSUNDEEP
excelvbacellisnumeric
Joka
Joka

Reputation: 65

How do you get Excel VBA to recognize numeric cells?

I have a table of numbers that are all left aligned (i.e. Excel recognizes them as text)

I run a VBA script on all cells:

cell.value = cell.Value * 1

This right aligns all of them and Excel recognized them as numbers except for decimals (e.g. 3.14 does not work while 314 works). I also run a find and replace script, where the search is for space (" ") and replace it with a blank(""), so this should get rid of atleast the common space.

Further clues: If i perform the =Value(A1) formula in Excel, Excel will recognize even the decimals as a number. If I run Workbookfunction.value(A1) Excel will not recognize as a number.

So the problem seems ro be related to VBA (?) and decimals. Any solutions?

I now ran the following after comments here:

For Each cell In rng
Dim vNumber As Double
On Error Resume Next
'Remove space
cell.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
   SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
  ReplaceFormat:=False

'Remove comma
cell.Replace What:=",", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
   ReplaceFormat:=False

'Check if empty, if it is: Do nothing
If IsEmpty(cell) = True Then

Else
vNumber = CDbl(cell.Value)
cell.Value = vNumber
End If

'Check if numeric
If IsNumeric(cell) = True Then
cell.Interior.Color = RGB(0, 254, 0)
cell.Interior.TintAndShade = 0.8

Else
cell.Interior.Color = RGB(100, 0, 0)
cell.Interior.TintAndShade = 0.8

End If
Next cell

The result is the following Before and After (where one is with Double and other with Variant. Somehow its writing over cells that are not decimals...

enter image description here

enter image description here

Upvotes: 1

Views: 2405

Answers (3)

Variatus
Variatus

Reputation: 14383

Val(Cells(1,1).Value will convert a string to a number if it's numeric, to zero if it's not. "123abc" will be converted to the number 123. IsNumeric(Cells(1,1).Value) will return True if there are no non-numeric characters in the cell's string.

Incidentally, VBA's Val() function will ignore blanks. Val(123 456") will return the number 123456.

The code below will meet your updated requirements. Please try it.

Sub ConvertTextToNumbers()

    Dim Rng As Range
    Dim Cell As Range
    Dim Arr As Variant
    Dim R As Long

    With Worksheets("Sheet1")           ' modify to suit
        Set Rng = .Range(.Cells(2, "B"), .Cells(.Rows.Count, "B").End(xlUp))
        Arr = Rng.Value

        For R = 1 To UBound(Arr)
            ' remove commas and spaces
            Arr(R, 1) = Val(Replace(Replace(Arr(R, 1), " ", ""), ",", ""))
        Next R

        Rng.Offset(0, 1).Value = Arr

        For Each Cell In Rng.Offset(0, 1)
            Cell.Interior.Color = IIf(Cell.Value, vbGreen, vbRed)
        Next Cell
    End With
End Sub

Upvotes: 1

Error 1004
Error 1004

Reputation: 8230

Did you try conversion?

Sub test()

    Dim rng As Range, cell As Range

    With ThisWorkbook.Worksheets("Sheet1")

        Set rng = .Range("A1:A5")

        For Each cell In rng
            .Range("B" & cell.Row).Value = CDbl(cell)
        Next cell

    End With

End Sub

Results:

enter image description here

Upvotes: 1

ACCtionMan
ACCtionMan

Reputation: 511

You need to convert the value of the cell to a double. For example:

Dim myDouble As Double
myDouble = CDbl(Range("A1").Value)
Debug.Print myDouble
myDouble = myDouble + 1
Debug.Print myDouble

Lines 3 to 5 are just to demonstrate that it gets recognised as a decimal.

Upvotes: 1

Related Questions