Copy source cell down a column

[rhudgins]

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?

[JBeaucaire]

Something like:

Please Login or Register  to view this content.

[rhudgins]

Thansk I can work with this!

[snb]

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

Please Login or Register  to view this content.

[JBeaucaire]

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.

Please Login or Register  to view this content.

[snb]

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:

Please Login or Register  to view this content.

[JBeaucaire]

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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

[romperstomper]

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...

[snb]

Looking for alternatives (the more choice the better)

Please Login or Register  to view this content.

[JBeaucaire]

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