Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
activecell.offset question
Hi all.
I'm tinkering with an idea for a macro and was able to create the following. ----------------- For i = 0 To 30 Selection.Copy ActiveCell.Offset(rowoffset:=4, columnoffset:=0).Activate ActiveSheet.Paste Next i End -------------------- It's supposed to copy an initial merged cell with contents, and then jump down to the next merged cell group (each merged cell group is 2 rows, 1 column). It runs for 30 iterations, and then stops. When I tried it I noticed that starting with row 25, it jumps one row, does the two row paste, jumps a row, does two, etc... out to the 30 iteration end. i.e., copy rows 1/2, jumps two rows, pastes 5/6, jumps to 9/10 & pastes, jumps two rows & pastes... etc.. out to rows 21/22. Then at row 25, it jumps over that, and pastes every 5th row. What have I done wrong? The code you see above is exactly what I'm running. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
activecell.offset question
Working with merged cells is a little tricky. Here is an example
of how to walk it down the range. Using the select and activate will cause you problems. You can use the i variable to set your row numbers for the range you want to cover and specify the column as I have done below. Then use the offset. bearing in mind that the numbers in parentheses count from the specified range down and right for positive numbers and up and left for negative numbers. The step 2 causes it to only execute on the merged cell one time. You will need to modify this to meet your needs. Sub luptst() For i = 1 To 8 Step 2 Range("A" & i).Copy Range("A" & i).Offset(4, 1).PasteSpecial Paste:=xlPasteValues Next End Sub "SteveDB1" wrote: Hi all. I'm tinkering with an idea for a macro and was able to create the following. ----------------- For i = 0 To 30 Selection.Copy ActiveCell.Offset(rowoffset:=4, columnoffset:=0).Activate ActiveSheet.Paste Next i End -------------------- It's supposed to copy an initial merged cell with contents, and then jump down to the next merged cell group (each merged cell group is 2 rows, 1 column). It runs for 30 iterations, and then stops. When I tried it I noticed that starting with row 25, it jumps one row, does the two row paste, jumps a row, does two, etc... out to the 30 iteration end. i.e., copy rows 1/2, jumps two rows, pastes 5/6, jumps to 9/10 & pastes, jumps two rows & pastes... etc.. out to rows 21/22. Then at row 25, it jumps over that, and pastes every 5th row. What have I done wrong? The code you see above is exactly what I'm running. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ActiveCell.Offset Question | New Users to Excel | |||
If activecell.column = variable then activecell,offset (0,1) | Excel Discussion (Misc queries) | |||
Activecell Offset | Excel Programming | |||
ActiveCell.Offset w/ VBA | Excel Programming | |||
activecell offset | Excel Programming |