Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does this work?
This works correctly
With Sheets("Running total") LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row .Cells(LastRow, "B").Resize(1, 28).Copy .Cells(LastRow + 1, "B") .Cells(LastRow, "B").Resize(1, 28).Value = .Cells(LastRow, "B").Resize(1, 28).Value End With Why do I need the resize (1, 28) twice on the last line to change the equations to values? But on the second line - I don't need it. Confused - can someone shed some light on the subject. Or if there is a better way to range value - please let me know. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does this work?
The first instance is a copy. When you copy a bunch of cells all you need to
do is to point to the single upper left cell where you would like to paste the results and the paste will paste the entire copied range, starting at that cell. Good so far? The next line is changing what was copied to values. In this case we take the values of the entire range and want to put them back in the same range. This is not a copy. You are making an array of the values in the cells and putting that array into the range that it came from... That is it in a nut shell... -- HTH... Jim Thomlinson "Brad" wrote: This works correctly With Sheets("Running total") LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row .Cells(LastRow, "B").Resize(1, 28).Copy .Cells(LastRow + 1, "B") .Cells(LastRow, "B").Resize(1, 28).Value = .Cells(LastRow, "B").Resize(1, 28).Value End With Why do I need the resize (1, 28) twice on the last line to change the equations to values? But on the second line - I don't need it. Confused - can someone shed some light on the subject. Or if there is a better way to range value - please let me know. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does this work?
Because the resize determines the size of the range being acted upon. In the
first instance, copy only needs to know the size of the source range, it will map that onto a similar sized target range. But the second instance is simulating pastespecdial values, it needs to know the size of the source range and the target range, otherwise it would not map correctly. For instance .Cells(LastRow, "B").Resize(1, 28).Value = .Cells(LastRow, "B").Value would copy the value of the first cell in that range to every cell in the range, not what is wanted. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Brad" wrote in message ... This works correctly With Sheets("Running total") LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row .Cells(LastRow, "B").Resize(1, 28).Copy .Cells(LastRow + 1, "B") .Cells(LastRow, "B").Resize(1, 28).Value = .Cells(LastRow, "B").Resize(1, 28).Value End With Why do I need the resize (1, 28) twice on the last line to change the equations to values? But on the second line - I don't need it. Confused - can someone shed some light on the subject. Or if there is a better way to range value - please let me know. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does this work?
You received good explanations. here is an approach that doesn't need the
resize multiple times. With Sheets("Running total") LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row .Cells(LastRow, "B").Resize(1, 28).Copy .Cells(LastRow + 1, "B").PasteSpecial xlValues .Cells(LastRow + 1, "B").PasteSpecial xlFormats End With -- Regards, Tom Ogilvy "Brad" wrote: This works correctly With Sheets("Running total") LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row .Cells(LastRow, "B").Resize(1, 28).Copy .Cells(LastRow + 1, "B") .Cells(LastRow, "B").Resize(1, 28).Value = .Cells(LastRow, "B").Resize(1, 28).Value End With Why do I need the resize (1, 28) twice on the last line to change the equations to values? But on the second line - I don't need it. Confused - can someone shed some light on the subject. Or if there is a better way to range value - please let me know. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does this work?
Thank you
"Jim Thomlinson" wrote: The first instance is a copy. When you copy a bunch of cells all you need to do is to point to the single upper left cell where you would like to paste the results and the paste will paste the entire copied range, starting at that cell. Good so far? The next line is changing what was copied to values. In this case we take the values of the entire range and want to put them back in the same range. This is not a copy. You are making an array of the values in the cells and putting that array into the range that it came from... That is it in a nut shell... -- HTH... Jim Thomlinson "Brad" wrote: This works correctly With Sheets("Running total") LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row .Cells(LastRow, "B").Resize(1, 28).Copy .Cells(LastRow + 1, "B") .Cells(LastRow, "B").Resize(1, 28).Value = .Cells(LastRow, "B").Resize(1, 28).Value End With Why do I need the resize (1, 28) twice on the last line to change the equations to values? But on the second line - I don't need it. Confused - can someone shed some light on the subject. Or if there is a better way to range value - please let me know. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does this work?
As always - appreciate the help.
As you probably already noticed - I had to make one final tweek from yesterday's help. "Bob Phillips" wrote: Because the resize determines the size of the range being acted upon. In the first instance, copy only needs to know the size of the source range, it will map that onto a similar sized target range. But the second instance is simulating pastespecdial values, it needs to know the size of the source range and the target range, otherwise it would not map correctly. For instance .Cells(LastRow, "B").Resize(1, 28).Value = .Cells(LastRow, "B").Value would copy the value of the first cell in that range to every cell in the range, not what is wanted. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Brad" wrote in message ... This works correctly With Sheets("Running total") LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row .Cells(LastRow, "B").Resize(1, 28).Copy .Cells(LastRow + 1, "B") .Cells(LastRow, "B").Resize(1, 28).Value = .Cells(LastRow, "B").Resize(1, 28).Value End With Why do I need the resize (1, 28) twice on the last line to change the equations to values? But on the second line - I don't need it. Confused - can someone shed some light on the subject. Or if there is a better way to range value - please let me know. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does this work?
If I wanted to do this
1. I would have to change ..Cells(LastRow + 1, "B").PasteSpecial xlValues to ..Cells(LastRow, "B").PasteSpecial xlValues - to get rid of the formulas and I would have to copy the equations to (LastRow + 1) before I do number 1 Correct? "Tom Ogilvy" wrote: You received good explanations. here is an approach that doesn't need the resize multiple times. With Sheets("Running total") LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row .Cells(LastRow, "B").Resize(1, 28).Copy .Cells(LastRow + 1, "B").PasteSpecial xlValues .Cells(LastRow + 1, "B").PasteSpecial xlFormats End With -- Regards, Tom Ogilvy "Brad" wrote: This works correctly With Sheets("Running total") LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row .Cells(LastRow, "B").Resize(1, 28).Copy .Cells(LastRow + 1, "B") .Cells(LastRow, "B").Resize(1, 28).Value = .Cells(LastRow, "B").Resize(1, 28).Value End With Why do I need the resize (1, 28) twice on the last line to change the equations to values? But on the second line - I don't need it. Confused - can someone shed some light on the subject. Or if there is a better way to range value - please let me know. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does this work?
LOL. I went the other way to avoid the pastespecial.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tom Ogilvy" wrote in message ... You received good explanations. here is an approach that doesn't need the resize multiple times. With Sheets("Running total") LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row .Cells(LastRow, "B").Resize(1, 28).Copy .Cells(LastRow + 1, "B").PasteSpecial xlValues .Cells(LastRow + 1, "B").PasteSpecial xlFormats End With -- Regards, Tom Ogilvy "Brad" wrote: This works correctly With Sheets("Running total") LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row .Cells(LastRow, "B").Resize(1, 28).Copy .Cells(LastRow + 1, "B") .Cells(LastRow, "B").Resize(1, 28).Value = .Cells(LastRow, "B").Resize(1, 28).Value End With Why do I need the resize (1, 28) twice on the last line to change the equations to values? But on the second line - I don't need it. Confused - can someone shed some light on the subject. Or if there is a better way to range value - please let me know. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does this work?
My apologies, I did misread your intent. This is what I believe you are
doing. With Sheets("Running total") LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row .Cells(LastRow, "B").Resize(1, 28).Copy .Cells(LastRow + 1, "B") .Cells(LastRow, "B").Resize(1, 28).Copy .Cells(LastRow, "B").PasteSpecial xlValues End With But is doesn't gain anything and may be worse because of the second copy - and looking at bob's comment, I suspect he must have suggested the original in an earlier thread. -- Regards, Tom Ogilvy "Brad" wrote: If I wanted to do this 1. I would have to change .Cells(LastRow + 1, "B").PasteSpecial xlValues to .Cells(LastRow, "B").PasteSpecial xlValues - to get rid of the formulas and I would have to copy the equations to (LastRow + 1) before I do number 1 Correct? "Tom Ogilvy" wrote: You received good explanations. here is an approach that doesn't need the resize multiple times. With Sheets("Running total") LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row .Cells(LastRow, "B").Resize(1, 28).Copy .Cells(LastRow + 1, "B").PasteSpecial xlValues .Cells(LastRow + 1, "B").PasteSpecial xlFormats End With -- Regards, Tom Ogilvy "Brad" wrote: This works correctly With Sheets("Running total") LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row .Cells(LastRow, "B").Resize(1, 28).Copy .Cells(LastRow + 1, "B") .Cells(LastRow, "B").Resize(1, 28).Value = .Cells(LastRow, "B").Resize(1, 28).Value End With Why do I need the resize (1, 28) twice on the last line to change the equations to values? But on the second line - I don't need it. Confused - can someone shed some light on the subject. Or if there is a better way to range value - please let me know. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to update a column in a work based on another work sheet | New Users to Excel | |||
how can i automatically generate work order numbers from work orde | Excel Discussion (Misc queries) | |||
flash object dont work in my excel work sheet | Excel Discussion (Misc queries) | |||
If I have a work sheet protected and try to run a macro to hide rows or columns it won't work. Correct? | Excel Programming | |||
Counting dates in multiple work sheets and work books | Excel Discussion (Misc queries) |