I have a formula in cell K2. How to copy this formula in every 72 cell
further in same column (K) up to cell rwo number 3269?

Range("K2").Copy _
Destination:=Range("K2:K3269")

To make it variable use another column to determine your last row like this

LastRow = Range("A" & Rows.Count).end(xlup).Row
Range("K2").Copy _
Destination:=Range("K2:K" & LastRow)


"Darius" wrote:

I have a formula in cell K2. How to copy this formula in every 72 cell
further in same column (K) up to cell rwo number 3269?

This fills up the whole K column with the formula while I want to copy the
formula in K2 in every cell with 72 rows distance. e.g. k2, k74, k146... up
to k3269

"joel" wrote:

Range("K2").Copy _
Destination:=Range("K2:K3269")

To make it variable use another column to determine your last row like this

LastRow = Range("A" & Rows.Count).end(xlup).Row
Range("K2").Copy _
Destination:=Range("K2:K" & LastRow)


"Darius" wrote:

I have a formula in cell K2. How to copy this formula in every 72 cell
further in same column (K) up to cell rwo number 3269?

Using an offset of 72 rows, you will end up short of Row 3269... the last
formula will end up in 3242. Anyway, here is code that copies your formula
as requested...

Sub CopyFormula()
Dim X As Long

Const Offset As Long = 72
Const StartRow As Long = 2
Const EndRow As Long = 3269
Const CopyColumn As String = "K"

For X = StartRow + Offset To EndRow Step Offset
Cells(StartRow, CopyColumn).Copy Cells(X, CopyColumn)
Next
End Sub

However, I am wondering if this is really what you want. You didn't show us
the formula or how you wanted the references in it to be increased. The
above formula advances the references relative to their addresses in the
original formula. So, a reference to B1 would become B3242 in that last
filled row.. you may, however, have wanted the values to increment
differently. If so, you will have to show us the formula and tells us what
you expected it to look like in, say K74... from that, we could figure out
how to set up a different method of incrementing your references (if that is
what you would want).

--
Rick (MVP - Excel)


"Darius" wrote in message
...
I have a formula in cell K2. How to copy this formula in every 72 cell
further in same column (K) up to cell rwo number 3269?

Not the most technically correct but this would work:

MyValue = Range("K2").Formula
Range("K2").Select

Do Until Activecell.Row 3269
Activecell(72,1).Select 'Selects the row 72 rows down from the current
location
Activecell = MyValue 'Enters the formula
Loop




"Rick Rothstein" wrote:

Using an offset of 72 rows, you will end up short of Row 3269... the last
formula will end up in 3242. Anyway, here is code that copies your formula
as requested...

Sub CopyFormula()
Dim X As Long

Const Offset As Long = 72
Const StartRow As Long = 2
Const EndRow As Long = 3269
Const CopyColumn As String = "K"

For X = StartRow + Offset To EndRow Step Offset
Cells(StartRow, CopyColumn).Copy Cells(X, CopyColumn)
Next
End Sub

However, I am wondering if this is really what you want. You didn't show us
the formula or how you wanted the references in it to be increased. The
above formula advances the references relative to their addresses in the
original formula. So, a reference to B1 would become B3242 in that last
filled row.. you may, however, have wanted the values to increment
differently. If so, you will have to show us the formula and tells us what
you expected it to look like in, say K74... from that, we could figure out
how to set up a different method of incrementing your references (if that is
what you would want).

--
Rick (MVP - Excel)


"Darius" wrote in message
...
I have a formula in cell K2. How to copy this formula in every 72 cell
further in same column (K) up to cell rwo number 3269?

Rick Rothstein wrote:

Cells(StartRow, CopyColumn).Copy Cells(X, CopyColumn)



I don't like using clipboard :)



Cells(X, CopyColumn).formular1c1 = Cells(StartRow, CopyColumn).formular1c1