Excel Vba宏插入十进制数

In my excel worksheet, if i try to insert a decimal number like 3.4 in a cell, the number that will appear is 3.4. But if i try to insert same number by vba macro, the number that appear is 3,4. This is my code.

在我的Excel工作表中,如果我尝试在单元格中插入像3.4这样的十进制数字,则显示的数字为3.4。但是,如果我尝试通过vba宏插入相同的数字,则显示的数字为3,4。这是我的代码。

Sub insertData()
   Dim valore as String
   valore = "3.4"
   Cells(1, 1) = valore
End Sub

How to solve this problem? Thanks

如何解决这个问题呢?谢谢

3 个解决方案

#1

There must be some formatting on your sheet as it worked fine here, but you can force the number format using below. for more decimal places just add them to the format e.g. "0.000" for 3 decimals

您的工作表上必须有一些格式,因为它在这里工作正常,但您可以使用下面强制数字格式。对于更多小数位,只需将它们添加到格式,例如“0.000”表示3位小数

Sub insertData()
Dim valore As String
valore = "3.4"
With Cells(1, 1)
    .Value = valore
    .NumberFormat = "0.0"
End With
End Sub

Below with cdbl converts the string to a double which should preserve the decimals too.

下面用cdbl将字符串转换为double,这也应该保留小数。

Sub insertData()
Dim valore As String
valore = "3.4"
With Cells(1, 1)
    .Value = cdbl(valore)
    .NumberFormat = "0.0"
End With
End Sub

#2

I do not find any thing wrong in your code. Perhaps sheet is formatted in different way from the original source. In such cases what I prefer to do is that I copy and paste to Note Tab Light text editor and then re paste the data to new excel work sheet.

我的代码中没有发现任何错误。也许工作表的格式与原始源不同。在这种情况下,我更喜欢做的是复制并粘贴到Note Tab Light文本编辑器,然后重新粘贴数据到新的excel工作表。

#3

Please, see this example. It will clarify my problem. This code make correct output.

请看这个例子。它将澄清我的问题。此代码可以正确输出。

Sub Macro1()
Dim row As Integer
Dim value As String
row = 1
For i = 1 To 3
For j = 1 To 3
For k = 1 To 3
value = i & "." & j & "." & k
Cells(row, 1) = value
row = row + 1
Next k
Next j
Next i
End Sub

This code make wrong output, because point is converted to comma.

此代码输出错误,因为point转换为逗号。

Sub Macro2()
Dim row As Integer
Dim value As String
row = 1
For i = 1 To 3
For j = 1 To 3
value = i & "." & j
Cells(row, 1) = value
row = row + 1
Next j
Next i
End Sub

#1