inserting excel formual dynamically

[padraigmc]

I want to insert a formula dynamically into a cell in an excel sheet. I am using the following line of code

ActiveCell.FormulaR1C1 = "=Subtotal(2, D2:A" & FinalRow & ")"

However in the excel sheet it includes qoutes like this

=SUBTOTAL(2, 'D2':'A256')

Does anyone now how to insert a formula like this correctly

Regards
padraigmc

[Al Chara]

First off, what is the range that you want to include in the subtotal?
Secondly, if you want to use R1C1 notation then cell D2 has to be entered as R2C4

Hope this helps.
Kind regards, Al.

[NateO]

The R1C1 methodology is inserting the quotes. Try this:

ActiveCell = "=Subtotal(2, D2:A" & FinalRow & ")"

Hope this helps.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-03-18 11:27 ]

[Cosmos75]

On 2002-03-18 10:40, Cosmos75 wrote:
I have two lists that are auto-generated on a page in Column A and Column B.

e.g.

Column A
Pens
23
31
44
55
67

Column B
Pencils
5
4
6
7
2

I want to be able to add at the end of the lists (after 67 for Pens and 2 for Pencils) , the Max value for each list using a macro.

The thing of it is, this list always changes in length so how do I get a marco to automatically choose the range that it needs to sum and add the sum to the end of the lists? How about adding the Sum or Average instead?

Lastly, I want to add a Conditional Format to the lists, I need the Max Value in the list to be bolded and changed the font color to red. Can this be done?

Please keep in mind that this list changes every time it generated, so I need to reset the ranges for the Max/Sum/Average and the Conditional Formatting each time.

Thanks!

How do I use this dynamic formula for finding the MAX in a range B2:??

I use ?? because I want to determins the ?? using Range("B65536").End(xlUp).Address or something akin to that? How do I get this work for any kind of formula?

[Al Chara]

To make it simpler you can place the following formula in a different column:

=max(b:b)

It will find the max in the entire column B.

[Cosmos75]

I have to many columns to do use MAX(B:B)

I changed the formula to this,
ActiveCell = "=MAX(D2:D" & FinalRow & ")"
but it puts =MAX(D2:D) in the row underneath the final row of the list. What does "& FinalRow &" do? Do I need to define it first?