Excel Automatically Formating Cells?

L

Larry Curcio

I am reading a file of decimal numbers into VBA. I can
read them into a variant, or into a DOUBLE. No matter.
When I assign the value to the cells of a worksheet, the
cells are automatically formatted to currency. As a result,
I have only two digits of precision.

Everywhere else in the program, I do such assignments
with no problem. Reformatting the cells outside the program
doesn't help.Converting the variant to a string works before the
assignment works but... why should I have to do this?

Any idea what I am doing wrong?

Thanks and Regards,
-Larry Curcio
 
D

Dave Peterson

Are you making sure that the cell is formatted as General first?

I tried this:
Option Explicit
Sub testme()

Dim myCell As Range
Dim myVal As Double

myVal = Application.Pi

Set myCell = ActiveSheet.Range("a1")
With myCell
.NumberFormat = "General"
.Value = myVal
Debug.Print "General: " & .Value
End With

Set myCell = myCell.Offset(1, 0)
With myCell
.NumberFormat = "$#,##0.00"
.Value = myVal
Debug.Print "Currency: " & .Value & "--Value2: " & .Value2
End With
End Sub


And got this back:
General: 3.14159265358979
Currency: 3.1416--Value2: 3.14159265358979

If you're checking the value (via code) of the cell after you've populated it,
you may want to use .value2.
 
L

Larry Curcio

Dave,

First, Thanks for the reply on the holiday.
As a matter of fact, I have tried setting the
number format of each cell just before assigning to it.
No help at all.

MySheet.Cells(ii + jj, 1).NumberFormat = "General"
MySheet.Cells(ii + jj, 1).Value = F

Result:
$8.76

.... Charming

Thanks,
-Larry Curcio
 
Top