Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to move a BLOCK of data using 'OFFSET'
To All,
I thought this would be a no brainer ... but I'm eating those words .... I have a userform that successfully allows the user to select a list entry. The ListBox1.ListIndex = 0 (-12)and are saved in a worksheet cell whose range-name is "ScheduleOffset" on a sheet called "DataSheet". My goal is simple ... HEADER_0 is the (DESTINATION) Range-name of the top-left-cell (in "A6") on DataSheet. I'm trying to copy 50 columns and 7 rows of data starting with/at HEADER_0. My SOURCE data starts at "BaseRow" (cell "A15")- which is the top left cell of the first block of data (50 columns x 7 rows)' All my other SOURCE blocks are 9 rows down from the preceeding block. "A24","A33","A42" ... etc. I calculate my row by multipling the 'value' stored in "ScheduleOffset" by 9. Then I "OFFSET" from "BaseRow" to get to the Top left cell of a specific BLOCK of data. Then I try to copy the 50 columns (includes 0-49) and 7 rows to my DESTINATION. The code fails to execute in the indicated line below ... CAN SOMEONE PLEASE SHOW ME HOW TO MOVE A BLOCK OF "VALUES" TO A DESTINATION RANGE???? ''''''''''''''''''''''''''''''''''' Sub CopyOffsetSchedule() Application.ScreenUpdating = False Sheets("DataSheet").Visible = True Sheets("DataSheet").Select Dim r As Integer: Dim c As Integer For r = 0 To 7 For c = 0 To 49 'FAILS on next line Range("BaseRow").Offset(9 * Range("ScheduleOffset").Value + r, c).Select Application.CutCopyMode = False Selection.Copy Range("HEADER_0").Offset(0, 0).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Next Next Sheets("DataSheet").Visible = False End Sub ''''''''''''''''''''''''''' Thanks in advance ... Jim Pellechi |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to move a BLOCK of data using 'OFFSET'
JimP,
Replace this part of the code Dim r As Integer: Dim c As Integer For r = 0 To 7 For c = 0 To 49 'FAILS on next line Range("BaseRow").Offset(9 * Range("ScheduleOffset").Value + r, c).Select Application.CutCopyMode = False Selection.Copy Range("HEADER_0").Offset(0, 0).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Next Next with this Range("BaseRow").Offset _ (9 * (Range("ScheduleOffset").Value) _ , 0).Resize(9, 50).Copy _ Destination:=Range("HEADER_0") HTH Cecil "JimP" wrote in message om... To All, I thought this would be a no brainer ... but I'm eating those words ... I have a userform that successfully allows the user to select a list entry. The ListBox1.ListIndex = 0 (-12)and are saved in a worksheet cell whose range-name is "ScheduleOffset" on a sheet called "DataSheet". My goal is simple ... HEADER_0 is the (DESTINATION) Range-name of the top-left-cell (in "A6") on DataSheet. I'm trying to copy 50 columns and 7 rows of data starting with/at HEADER_0. My SOURCE data starts at "BaseRow" (cell "A15")- which is the top left cell of the first block of data (50 columns x 7 rows)' All my other SOURCE blocks are 9 rows down from the preceeding block. "A24","A33","A42" ... etc. I calculate my row by multipling the 'value' stored in "ScheduleOffset" by 9. Then I "OFFSET" from "BaseRow" to get to the Top left cell of a specific BLOCK of data. Then I try to copy the 50 columns (includes 0-49) and 7 rows to my DESTINATION. The code fails to execute in the indicated line below ... CAN SOMEONE PLEASE SHOW ME HOW TO MOVE A BLOCK OF "VALUES" TO A DESTINATION RANGE???? ''''''''''''''''''''''''''''''''''' Sub CopyOffsetSchedule() Application.ScreenUpdating = False Sheets("DataSheet").Visible = True Sheets("DataSheet").Select Dim r As Integer: Dim c As Integer For r = 0 To 7 For c = 0 To 49 'FAILS on next line Range("BaseRow").Offset(9 * Range("ScheduleOffset").Value + r, c).Select Application.CutCopyMode = False Selection.Copy Range("HEADER_0").Offset(0, 0).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Next Next Sheets("DataSheet").Visible = False End Sub ''''''''''''''''''''''''''' Thanks in advance ... Jim Pellechi |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to move a BLOCK of data using 'OFFSET'
Works like a charm, THANKS
JimP *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Move offset in scatterplot | Charts and Charting in Excel | |||
move from active cell offset in macro | Excel Discussion (Misc queries) | |||
My numbers turn into # signs, when I move to the next block | Excel Worksheet Functions | |||
Sort Block of Data | Excel Worksheet Functions | |||
Re-Arranging a Block of Data | Excel Programming |