VBA，处理E-10

Question

我相信我的细胞应该是0。7.45058059692383E-12。如何在代码中生成这些0？

Sheets("MainData").Range("C" & i) = Sheets("CM_MainData").Range("C" & i)

我认为将NumberFormat转换为0可能有帮助，但它没有起作用。

Sheets("MainData").Range("C" & i) = Sheets("CM_MainData").Range("C" & i).NumberFormat = "0.00"

相反，结果是FALSE

请帮帮忙！

完整代码：

Sub CopyData()

Worksheets("MainData").Rows("2:" & Rows.Count).ClearContents

'Copy data from the CM Commentary File to Template
Application.ScreenUpdating = False
 
Set MainDataCM = Workbooks.Open(Sheets("Input").Range("B3") & Sheets("Input").Range("B6"))
MainDataCM.Sheets("Main Data").Copy After:=ThisWorkbook.Sheets(1)
MainDataCM.Close savechanges:=False

Application.ScreenUpdating = True

Sheets("Main Data").Name = "CM_MainData"

Worksheets("CM_MainData").Visible = False

'Read the CM_MainData tab and copy the required columns in the MainData tab
Dim k As Long
k = Sheets("CM_MainData").Range("A1", Sheets("CM_MainData").Range("A1").End(xlDown)).Rows.Count
Debug.Print (k)

i = 2

While i <= k
    Sheets("MainData").Range("A" & i) = Sheets("CM_MainData").Range("A" & i)
    Sheets("MainData").Range("B" & i) = Sheets("CM_MainData").Range("B" & i)
    Sheets("MainData").Range("C" & i) = Sheets("CM_MainData").Range("C" & i).NumberFormat = "0.00"
    Sheets("MainData").Range("D" & i) = Sheets("CM_MainData").Range("D" & i)
    Sheets("MainData").Range("E" & i) = Sheets("CM_MainData").Range("C" & i) * 1000
    Sheets("MainData").Range("F" & i) = Sheets("CM_MainData").Range("H" & i)
    'Sheets("MainData").Range("E" & i).NumberFormat = "0.00"
    
    If Sheets("MainData").Range("F" & i) = "" Then
        Sheets("MainData").Range("F" & i) = "RBC INVESTOR SERV O/H & MISC"
    End If
    
    i = i + 1
    
Wend

Worksheets("Macro").Activate
Worksheets("Macro").Select

MsgBox "Step 1 Completed"

End Sub

Answer 1

价值转移：

Sheets("MainData").Range("C" & i).Value = Sheets("CM_MainData").Range("C" & i).Value

数字格式(单独的步骤，不更改基础值)：

Sheets("MainData").Range("C" & i).NumberFormat = "0.00"

或者使用WorksheetFunction.Round (确实会更改基础值)。

Sheets("MainData").Range("C" & i).Value = WorksheetFunction.Round(Sheets("CM_MainData").Range("C" & i).Value, 2)