inserting excel formual dynamically

padraigmc

New Member
Joined
Mar 11, 2002
Messages
2
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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.
 
Upvote 0
The R1C1 methodology is inserting the quotes. Try this:

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

Hope this helps.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-03-18 11:27
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top