Thread: Copy source cell down a column

I have a formula in cell JI 104 that I need to copy every 7 rows down column JI. JI 111, JI 118, JI 125 ect.. until the end of my data set. Can someone help me write this macro to locate the source cell and then copy it every 7 rows down the column?

Something like:

Dim LR as Long, Rw as Long

LR = Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious).Row

For Rw = 111 to LR Step 7
    Range("JI104").Copy Range("JI" & Rw)
Next Rw

If you need rocket speed:
avoid copying
reduce writing operations.

Sub tst()
  For j = 111 To Sheets(1).UsedRange.Rows.Count Step 7
    c01 = c01 & "," & Cells(j, 269).Address
  Next
  Range(Mid(c01, 2)) = Cells(104, 269).Formula
End Sub

1) The use of USED RANGE in this manner is fraught with hidden errors that are hard to ID when they occur (they don't always occur). Because of it's unreliability I never use USEDRANGE in this manner.

2) If speed is an actual issue, then SNB is correct, you can do the copy/paste in one fell swoop.

Dim CpyRNG as Range, LR as Long, Rw as Long

LR = Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious).Row

'Create a range to copy to later
    For Rw = 111 to LR Step 7
        If CpyRNG Is Nothing Then
            Set CpyRNG = Range("JI" & Rw)
        Else
            Set CpyRNG = Union(CpyRng, Range("JI" & Rw))
        End If
    Next Rw

'Copy all at once
    Range("JI104").Copy CpyRNG

The charm of usedrange is that it is exactly what it's name indicates.
The problem however is that many users do not realise that non-used rows or columns (left from of above data) are not part of the usedrange.
But in the western world there's a strong tendency to work from the left upper corner to a right lower one. So more often than not cell A1 isn't empty.

An alternative could be:

Sub snb()
  For j = 111 To Sheets(1).Cells.SpecialCells(11).Row Step 7
    c01 = c01 & "," & Cells(j, 269).Address
  Next
  Range(Mid(c01, 2)) = Cells(104, 269).Formula
End Sub

Excel is notorious for getting the "Last Cell" wrong, too. Because of the inaccuracies possible by quick use of the .UsedRange method and the .SpecialCells(xlCellTypeLastCell) method, I stopped using them both long ago.

I underline, both methods do work on simple datasets (or appear to), but when they do fail, you won't know it's happened and figuring it out is frustrating.

Therefore, the two methods I utilize for accurately spotting the "last row in use" are:

1) Always use a specific column:

LR = Range("A" & Rows.Count).End(xlUp).Row

2) Search from the bottom for last row with data regardless of the column that happens to be:

LR = Cells.Find("*", Cells(Rows.Count, Columns.Count), _
         SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

The problem is that UsedRange is occasionally inaccurate and includes rows or columns that are no longer in use. There's also a limit to how long a String you can use when referring to ranges...

Looking for alternatives (the more choice the better)

Sub tst()
  With Cells(111, 269).Resize(Sheets(1).Cells.SpecialCells(11).Row - 110)
    .Value = Evaluate(Replace("IF(MOD(ROW(" & .Address & ")-111,7)=0,#" & Cells(104, 269).Formula & "#,IF(" & .Address & "=##,##," & .Address & "))", "#", Chr(34)))
  End With
End Sub

Unfortunately this doesn't resolve the problem wherein Excel occasionally thinks the LastCell is far away from where it actually is.