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/ |
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/ |
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/ |
All times are GMT +1. The time now is 03:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com