Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to add to a formula (2003)

    I need a macro that will add an expression to an existing formula in a selected cell. Let's say I am in cell a 100 and there is a formula that reads "=Sum(a1:a99)" and I want to add a +50. Now, if I were in cell b45 or any other cell and wanted to add the same "+50" I should be able to invoke this macro to do so.

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to add to a formula (2003)

    You could use this:
    <code>
    Sub Add2Formula()
    If ActiveCell.HasFormula Then
    ActiveCell.Formula = ActiveCell.Formula & "+50"
    End If
    End Sub
    </code>
    or if you want to decide what to add on the fly:
    <code>
    Sub Add2Formula()
    If ActiveCell.HasFormula Then
    ActiveCell.Formula = ActiveCell.Formula & InputBox("What do you want to add?")
    End If
    End Sub
    </code>
    The latter doesn't check whether the addition is valid.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to add to a formula (2003)

    Thanks Hans. The second formula worked fine.

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to add to a formula (2003)

    A non-macro method would be to:

    <UL><LI>enter 50 in a blank cell
    <LI>copy that cell
    <LI>do an Edit/Paste Special... on the cell(s) you want to add the 50
    <LI>Choose Add in the Operation section - click okay[/list]If the cell has a formula, the original formula is surrounded by parenthesis and the +50 is tacked on to the end of the formula. If the cell has a value, the value will increase by 50.

Posting Permissions

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