Copying Cells
 

Hello,

I'm using Excel 97

Maybe someone can explain this to me. I'm copying, via code, the
contents of one cell to another cell. The source cell contains a 16
decimal place number, and is formatted as currency with 4 decimal
places. (0.0160690694272642 stored, $0.0161 displayed).

With code Cells(1, 1).Copy Cells(1,3), I end up with the complete
number copied, and $0.0161 displayed. But Cells(1, 3) = Cells(1, 1)
only copies the 0.0161. However, if I remove the formatting from the
source cell, both methods produce the same result, and copy the
complete number. I haven't run into this one before. I thought the 2
methods were equivalent, but apparently not all the time.

Appreciate your help,

Regards,

DaveU

Copying Cells
 

Dave,
That makes sense to me.
Using .Copy, you are passing all the info on that cell; .value,
formatting, borders etc because you are effectively passing a Range
object.

With "Cells(1, 3) = Cells(1, 1)", you are only setting the default
property (.Value); there is no other information involved.

Without the formatting, the 2 methods then produce the same results.

NickHK

Dave Unger wrote:
Hello,

I'm using Excel 97

Maybe someone can explain this to me. I'm copying, via code, the
contents of one cell to another cell. The source cell contains a 16
decimal place number, and is formatted as currency with 4 decimal
places. (0.0160690694272642 stored, $0.0161 displayed).

With code Cells(1, 1).Copy Cells(1,3), I end up with the complete
number copied, and $0.0161 displayed. But Cells(1, 3) = Cells(1, 1)
only copies the 0.0161. However, if I remove the formatting from the
source cell, both methods produce the same result, and copy the
complete number. I haven't run into this one before. I thought the 2
methods were equivalent, but apparently not all the time.

Appreciate your help,

Regards,

DaveU

Copying Cells
 

Currency and dates can be trouble.

Instead of using:
cells(1,3).value = cells(1,1).value
(.value is the default property that you're relying on)

Use:
cells(1,3).value2 = cells(1,1).value2

VBA's help for .value2 will explain(?) how those Currency and date types are a
PITA.

Dave Unger wrote:

Hello,

I'm using Excel 97

Maybe someone can explain this to me. I'm copying, via code, the
contents of one cell to another cell. The source cell contains a 16
decimal place number, and is formatted as currency with 4 decimal
places. (0.0160690694272642 stored, $0.0161 displayed).

With code Cells(1, 1).Copy Cells(1,3), I end up with the complete
number copied, and $0.0161 displayed. But Cells(1, 3) = Cells(1, 1)
only copies the 0.0161. However, if I remove the formatting from the
source cell, both methods produce the same result, and copy the
complete number. I haven't run into this one before. I thought the 2
methods were equivalent, but apparently not all the time.

Appreciate your help,

Regards,

DaveU

--

Dave Peterson

Copying Cells
 

Hello,

Thanks to all for your replies. Before running into this scenario, I
thought that the Value property of a cell was what was actually stored
in the cell (what you see in the Formula bar), and all that formatting
did was change what was displayed in the cell. Obviously, my
assumption was incorrect, although I'm still not clear as to why.
I'll have to think about this for a while.

Dave, from what you've said, I'm assuming the Value2 property
should always be used when working with Date and Currency formats. Do
you need the Value2 on both sides of the equation? Both of these lines
seemed to produce the same (correct) results in this instance.

Cells(1, 3).Value = Cells(1, 1).Value2
Cells(1, 3).Value2 = Cells(1, 1).Value2

Thanks for your help,

Regards,

DaveU

Copying Cells
 

I'd use the .value2 property on both sides.

I'm scared enough by having to use .value2 with currence/dates that I'd use it
even if it wasn't required!

And I agree: VBA's help explains that you have to use it--not a very good
explanation of why, though. But that's the way most helps are--just do it,
don't ask why <bg.



Dave Unger wrote:

Hello,

Thanks to all for your replies. Before running into this scenario, I
thought that the Value property of a cell was what was actually stored
in the cell (what you see in the Formula bar), and all that formatting
did was change what was displayed in the cell. Obviously, my
assumption was incorrect, although I'm still not clear as to why.
I'll have to think about this for a while.

Dave, from what you've said, I'm assuming the Value2 property
should always be used when working with Date and Currency formats. Do
you need the Value2 on both sides of the equation? Both of these lines
seemed to produce the same (correct) results in this instance.

Cells(1, 3).Value = Cells(1, 1).Value2
Cells(1, 3).Value2 = Cells(1, 1).Value2

Thanks for your help,

Regards,

DaveU

--

Dave Peterson

Copying Cells
 

Dave, thanks again for this.

DaveU