Re: Copy contents of cells down

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Tom Ogilvy (twogilvy_at_msn.com)
Date: 03/31/04 

Date: Wed, 31 Mar 2004 12:09:04 -0500

rather than do

cells(2,2).Formula = "=some formula"

do

Dim rng as Range
With Worksheets("Destination")
set rng = .Range(.cells(1,2),.cells(rows.count,1).End(xlup))
End with
rng.offset(0,1).Formula = "=some formula"
rng.offset(0,2).formula = "=some other formula"

this will put the formula in all the cells. 

-- 
Regards,
Tom Ogilvy
"Dominique Feteau" <freekdominique@hotmail.com> wrote in message
news:%23KOCi5zFEHA.3880@TK2MSFTNGP10.phx.gbl...
> I recorded a macro that does a number of things. (filters a table, selects
a
> defined range, copies it into another work***, adjusts the column
widths,
> inserts 3 new columns and names them, and inserts 2 formulas in the 2nd
row
> of 2 of the new columns).
>
> What I want to do i copy the formula down, but I only know how to do that
if
> the number of rows are static.  How can I tell the macro to count how many
> rows there are and copy the contents of a given cell that number of rows.
> Or have it start to copy down, but have it look to the adjacent cell to
see
> if its empty.  if its empty, then stop copying.
>
> any solutions would be greatly appreciated.
>
> thanks
> niq
>
>
Quantcast