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.
I've tried using C.value = c.value after setting the NumberFormat to numbers or general, but that does nothing for me
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.