Copy formula and paste same row to last column used

My data is not set up the same as yours, but in general the issue is you are trying to set a range by mixing address types (R1C1 and "R1").

Using some test data I do not get the correct value for iLastColumn (I think), so here is my formula:

Please Login or Register  to view this content.

Where: Cells(1, 18) represents your "R1" and
Cells(1, 18 + iLastColumn) represents iLastColumns after "R1"

If you supply a sample of your data I may be able to clean this up, otherwise this should get you going in the right direction.

Hello Chance2,

It looks like it is working the way it is suppose too. However, for some reason it will only go out two columns. What would be the proper reference for iLastColumn?

The series of macro's that this project runs is rather extensive, so I am breaking it up into small pieces, trying to make each of the subtotal routines more dynamic.

Here is the code between defining the variables and the pasting of the formula you showed.

Please Login or Register  to view this content.

Does any of this explain why the code would only extend out two columns from Cells(1, 18)?? In this case my last column used should be AL.

Thanks,
Matt

I did not debug all your code (it looks like iLastColumn is using ROW not COLUMN), but I would recommend trying a different iLastColumn formula:

Please Login or Register  to view this content.

And then just use Cells(1, iLastColumn) as the second reference in the Range (take out the 18 +).

Hello Chance2,

Something is going weird.

Perhaps I go back to the basics. This was just one of the subtotal calculations I needed to do. So maybe instead of copying them individually, I do all 6 of them at the same time.

Here is the what it looks like:

R1:R6 need to have their formula copied all the way to the last used column which can be found using Row 7.

Maybe this makes it easier to deal with.

Regards,
Matt

Hello Chance2,

I think I figured it out. Here is what I did:

Please Login or Register  to view this content.

Seems to work.

Thanks for your help!! My next question will be about making my pivot table more dynamic...stay tuned:-)

Cheers,
Matt