inserting excel formual dynamically
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: inserting excel formual dynamically

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  5. #5
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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?

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com