Excel Automatically Formating Cells?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Larry Curcio - 25 Dec 2005 05:03 GMT

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

Reply to this Message

Dave Peterson - 25 Dec 2005 13:48 GMT

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.

> I am reading a file of decimal numbers into VBA. I can
> read them into a variant, or into a DOUBLE. No matter.
[quoted text clipped - 11 lines]
> Thanks and Regards,
> -Larry Curcio

Signature

Dave Peterson

Reply to this Message

Larry Curcio - 25 Dec 2005 14:28 GMT

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

> Are you making sure that the cell is formatted as General first?
>
[quoted text clipped - 44 lines]
> > Thanks and Regards,
> > -Larry Curcio

Reply to this Message

Dave Peterson - 25 Dec 2005 16:28 GMT

What's F Dimmed as and what's in it at the time of the assignment?

> Dave,
>
[quoted text clipped - 67 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message