Basic Offset Function Usage

  • Thread starter Thread starter Novice
  • Start date Start date
N

Novice

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.
 
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)
 
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.
 
Back
Top