VBA: Convert number format / refresh cell-CodePudding

I have a problem that seams simple.

I have some cells that I can convert easily in Excel but I can't find a simple way to convert it in VBA.

The cells contains numbers in the format 35,00 and act like numbers (I can add them). But the aren't concidered as the same as number format 35.

If I click in the cell they will transform in the 35 format and therefore be considered as such.

Why does it matter? Because of the code Cells(WorksheetFunction.Match(r.Offset(, -4), Sheets(4).Range("I3:I50"), 0) 2, 9) I'm using. The code works well if the format is 35, but doesn't work if the format is 35,00.

Here's what I've tried to convert the 35,00 in 35.

What doesn't work in Excel:

Changing format (for standard or number)
Copy/paste

What works in Excel:

Double-click in the cell and leave
Copy nothing and add it to the cell
Replace "," with ","

What doesn't work in VBA:

Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic

Range("A3").Calculate

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

With Selection .NumberFormat = "0" .Value = .Value End With

Range("I1:I40") = Range("I1:I40").Value

Range("I2").Select
Application.CutCopyMode = False
Selection.Copy
Range("G:I").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:= _
    False, Transpose:=False

Columns("D:F").Select
Selection.Replace What:=",", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

What does work in VBA:
1.

Selection = CLng(Selection)   '-- work only for 1 cell

In order to make it work, I would have do a For Each which seams a bit too much for what I need. Is there a way to convert a range at once?

It's weird that when I record a macro while fixing the issue in Excel (like replace "," by ","), the VBA code generated doesn't work.

Here's a sample file with the indomitable:

https://docs.google.com/spreadsheets/d/1sSUo3tTORe6xLmj38EoMT0-SopNdr3Uk/edit?usp=sharing&ouid=112578955965682987037&rtpof=true&sd=true

CodePudding user response：

Here's the best I've got so far:

Selection.TextToColumns Destination:=Range("1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True

It converts 1 column at the time.

CodePudding user response：

vba:

Edit:

It appears that your system decimal symbol and the decimal symbol on the worksheet may be different
In that case, you must first correct that
It would be easiest to correct that at the time you import your data, as it is usually an option in the data import method
Failing that, try this VBA macro:

Option Explicit
Sub text2num()
    Dim range2Convert As Range, c As Range
    
Set range2Convert = ThisWorkbook.Worksheets("sheet9").Range("d129:d131")
    For Each c In range2Convert
        c.NumberFormat = "General"
        c.Value = WorksheetFunction.NumberValue(c.Value, ",", ".")
    Next c
End Sub

Notes -Change the Range2Convert argument as appropriate for your workbook -Note that the VBA NumberValue function seems to require specifying both the decimal and the thousands separator. I guessed at the thousands -- you may want to change that to match whatever it is for the incoming data, if not a dot. No need if it isn't used