Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basic Offset Function Usage
I'm a beginner so this will be a stupid question. How do I use the offset function with a counter as my row number? What I'm trying to do is paste data from sheet 1 into sheet 2 using offset with the row number increased by 14 each time. For example: Counter =14 ActiveCell.FormulaR1C1="Offset(Sheet1!R[13]C[-1],Counter,0)" Counter=Counter +14 It doesn't work. I realize I should use the Cells Function, but I do not know how to do it. The data in sheet 1 is in rows that are 14 rows apart in column A. I want to post the data in sheet 2 directly underneath each other in one column. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basic Offset Function Usage
If you want to paste (as opposed to creating a referencing formula as you
attempt in your code), try this iRow = 1 Counter = 14 Cells(Counter, "A").Copy Destination:=Worksheets("Sheet2").Cells(iRow, "A") iRow = iRow + 1 Counter = Counter + 14 Cells(Counter, "A").Copy Destination:=Worksheets("Sheet2").Cells(iRow, "A") etc. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Novice" wrote in message ... I'm a beginner so this will be a stupid question. How do I use the offset function with a counter as my row number? What I'm trying to do is paste data from sheet 1 into sheet 2 using offset with the row number increased by 14 each time. For example: Counter =14 ActiveCell.FormulaR1C1="Offset(Sheet1!R[13]C[-1],Counter,0)" Counter=Counter +14 It doesn't work. I realize I should use the Cells Function, but I do not know how to do it. The data in sheet 1 is in rows that are 14 rows apart in column A. I want to post the data in sheet 2 directly underneath each other in one column. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basic Offset Function Usage
icol = 3 ' column C, change to the column where you want the values
Cells(1,icol).Formula = "=Sheet1!$A$1" lastrow = worksheets("Sheet1").Cells(rows.count,1).End(xlup) .row j = 2 for i = 15 to lastrow step 14 cells(j,icol).Formula= "=Offset(Sheet1!$A$1," & i-1 & ",1)" Next Assumes the first value is in A1 and the next value is in A15 where pattern continues. Sheet2 is the active sheet when you run the macro. -- Regards, Tom Ogilvy Novice wrote in message ... I'm a beginner so this will be a stupid question. How do I use the offset function with a counter as my row number? What I'm trying to do is paste data from sheet 1 into sheet 2 using offset with the row number increased by 14 each time. For example: Counter =14 ActiveCell.FormulaR1C1="Offset(Sheet1!R[13]C[-1],Counter,0)" Counter=Counter +14 It doesn't work. I realize I should use the Cells Function, but I do not know how to do it. The data in sheet 1 is in rows that are 14 rows apart in column A. I want to post the data in sheet 2 directly underneath each other in one column. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a test for basic excell knowledge/usage???? | Excel Discussion (Misc queries) | |||
how format date with TEXT() function for international usage? | Excel Worksheet Functions | |||
About the usage of the function | Excel Worksheet Functions | |||
Function usage and outcome clarification | Excel Worksheet Functions | |||
How to apply OFFSET as the range in a basic 'Copy' process... | Excel Discussion (Misc queries) |