CODEWITHSUNDEEP
excelvba
Keyser Soze
Keyser Soze

Reputation: 272

Decimal number conversion VBA

Below is an example of the table I have:

enter image description here

When I try to divide the whole "I" column by a number, I get an Incompatibility error since Excel uses Commas instead of dots for decimals. Any one-liner to change the format of the column without looping please ? or any easy alternative solutions

Upvotes: 0

Views: 473

Answers (3)

JvdV
JvdV

Reputation: 75840

As per my comment, you could apply the Excel's NUMBERVALUE() formula where you assert that there could be a decimal point:

=NUMBERVALUE(<YourRange>,".")/<YourNumber>

The VBA-Equivalent could be:

Sub Test()

Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
Dim lr As Long: lr = ws.Range("I" & ws.Rows.Count).End(xlUp).Row
Dim rng As Range: Set rng = ws.Range("I2:I" & lr)
Dim nr As Long: nr = 5

rng.Value = Application.Evaluate("=NUMBERVALUE(" & rng.Address & ",""."")/" & nr)

End Sub

enter image description here >> enter image description here

I opted to use Application.Evaluate method because you mentioned you also need to devide the values by a static number. If you didn't have to do so, you could opt for:

With Application
    rng.Value = .Index(.NumberValue(rng, "."),0,1)
End With

enter image description here >> enter image description here

Upvotes: 2

FaneDuru
FaneDuru

Reputation: 42236

Please, try the next VBA way:

Sub ChangeDecimalSep()
 Dim ws As Worksheet, rng As Range, lastRow As Long
 
 Set ws = ActiveSheet
 
 lastRow = ws.Range("I" & ws.rows.count).End(xlUp).Row
 Set rng = ws.Range("I2:I" & lastRow)
 
 With rng
    .replace ".", ",", xlPart
    .NumberFormat = "General"
    .Value = .Value
 End With
End Sub

The above code supposes that the column to be processed is "I:I", starting from its second row, in the first one being the header. If different, it would be easy to adapt it, I think...

Upvotes: 1

andrewb
andrewb

Reputation: 1146

You can just select the whole column, press Ctrl + h, put period in "Find what" and comma in "Replace with". Press "Replace all" and problem solved. Excel will automatically convert the values to numbers.

This is the vba equivalent:

Sub periodToComma()
    Dim targetRange As Range
    Set targetRange = Columns("A:A")
    
    targetRange.Replace What:=".", Replacement:=Application.DecimalSeparator, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
End Sub

But this will not convert the values to numbers for some reason. There are some solutions floating around like adding

With targetRange
    .NumberFormat = "General"
    .Value = .Value
End With

But that does not work for me.

However, if all you need is to perform division or other basic arithmetics on the values in the column, this will work.

Upvotes: 1

Related Questions