excel - VBA, Dealing with E-10

Question

I have cells that are supposed to be 0 I believe. 7.45058059692383E-12. How do I make these 0 in my code?

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

I thought converting NumberFormat to 0 may help but it did not work.

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

Instead the result is FALSE

Please Help!

Full code:

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

question from:https://stackoverflow.com/questions/65851685/vba-dealing-with-e-10

Answer 1

Value transfer:

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

Number format (a separate step, and does not change the underlying value):

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

Or use WorksheetFunction.Round (does change the underlying value).

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