Copy a formula down depending on a range

[john434]

Hi,

I produce a spreadsheet from a database weekly in oder to show activity on a week to week basis.

I then use this spreadsheet to create a pivot table so the information is easy to understand.

I want to create a macro that creates a dynamic range, adds a couple of formuala's and creates the pivot table(s).

I'm stuck on the creation of the formula's.

How can i get excel to copy the formular down to the end of the range without dragging it manually?

hope i've explained the problem correctly.

Thanks in advance

John

 

[AnotherHiggins]

Hi john434,

There are lots of ways to accomplish this. Here's the first one that comes to mind.

Let's say that your calculation is in column C and that you want to copy it down as far as column B is populated. You could use this to subtract column A from column B:
[COLOR=blue white]range("c2", range("b65536").end(xlup).offset(0,1))= "=RC[-1]-RC[-2]"[/color]

Just exchange the formula in quotes with whatever you really want to do.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[AnotherHiggins]

P.s. Just for future reference, VBA questions would be better posted in forum707, "VBA Visual Basic for Applications (Microsoft) Forum".

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886