将存储为文本的数字转换为数字 - 小数

I realise that this is sort of a duplicate question, but the solutions mentioned in this question don't work for me completely.

我意识到这是一个重复的问题，但这个问题中提到的解决方案并不适合我。

My current code is as follows

我目前的代码如下

Sub ConvertTextToNumber()
Dim Area As Range, C As Range
Set Area = Sheets("Tank 3").Range("C7:L7,B8:B17,C20:L20,B21:B69")

For Each C In Area
    If WorksheetFunction.IsNumber(Celle) = False And C <> "" Then
        C.NumberFormat = "0.0"
        C.Value = C.Value + 0
    End If
Next C

End Sub

This works fine on integers, but the moment the number stored as text is a decimal such as 0,1 or 0,2, it simply converts the number to 1 or 2 respectively.

这对整数有效，但是当存储为文本的数字是一个小数，如0,1或0,2时，它只是将数字分别转换为1或2。

I've tried using C.value = c.value after setting the NumberFormat to numbers or general, but that does nothing for me

在将NumberFormat设置为数字或一般后，我尝试使用C.value = c.value，但这对我没有任何作用

Update: It seems the problem lies with the separators. I have to use "," as the decimal separator, and this makes the sub malfunction. If I swap the decimal separator to "." the sub runs fine.

更新：似乎问题在于分隔符。我必须使用“，”作为小数分隔符，这会导致子故障。如果我将小数点分隔符交换为“。”子运行正常。

3 个解决方案

#1

Simply use Range.FormulaLocal to automatically cast a string to a number based on your regional settings:

只需使用Range.FormulaLocal根据您的区域设置自动将字符串转换为数字：

Sub ConvertTextToNumber()
  Dim Area As Range, C As Range
  Set Area = Sheets("Tank 3").Range("C7:L7,B8:B17,C20:L20,B21:B69")

  For Each C In Area
      C.FormulaLocal = C.Value
  Next

End Sub

#2

You can use a little trick and that would be to import all the numbers as text in to your code then manipulate it and spit it back out. The comma confuses english conversions and would see 2,0 as 20. Anyway here's how you could do it

您可以使用一个小技巧，那就是将所有数字作为文本导入到您的代码中然后操纵它并将其吐出来。逗号混淆了英语转换，并将2,0视为20.无论如何，这里是你如何做到的

Sub ConvertTextToNumber()

  Dim Area As Range, C As Range
  Dim InNumAsStr As String

  Set Area = Sheets("all").Range("A1:B10")

    For Each C In Area
      InNumAsStr = C.Text '*** force the value to be a string type ***
      '***swap out any commas with decimal points ***
      InNumAsStr = Replace(InNumAsStr, ",", ".") 
      C.NumberFormat = "0.0"
      C.Value = InNumAsStr
   Next C

End Sub

#3

You can change the separators through VBA:

您可以通过VBA更改分隔符：

Sub ChangeSeparator()
    Application.DecimalSeparator = "."
    Application.ThousandsSeparator = ","
    Application.UseSystemSeparators = False
    ConvertTextToNumber
    Application.UseSystemSeparators = True
End Sub

#1