Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Cells
Dave, thanks again for this. DaveU |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying cells | Excel Discussion (Misc queries) | |||
copying cells by checking color of the cells | Excel Programming | |||
How do I skip blank cells when copying over a range of cells? | Excel Discussion (Misc queries) | |||
Excel should allow cloaking of certain cells when copying cells b. | Excel Worksheet Functions | |||
Copying cells | Excel Programming |