Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Nov 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro Help - Adding std formula to cell contents (Office 2003/XP)

    I

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

    Re: Macro Help - Adding std formula to cell contents (Office 2003/XP)

    Why would you want a cell to contain a formula such as =3.456*2? You may not need formulas, and not need a macro to do this.
    - Enter 2 into an empty cell.
    - Copy this cell to the clipboard.
    - Select the target cells.
    - Select Edit | Paste Special...
    - Click the Multiply option.
    - Click OK.

  3. #3
    New Lounger
    Join Date
    Nov 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Help - Adding std formula to cell contents (Office 2003/XP)

    You probably saw my post before I edited it to address the very same query - the problem is that with the paste special, I lose the source numbers. I need these to remain available for future changes (very near future, as this is a simulation).

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

    Re: Macro Help - Adding std formula to cell contents (Office 2003/XP)

    You could do something like this. It will work if the selection consists of cells with numeric values only.

    Sub MultiplyCells()
    Dim oCell As Range
    For Each oCell In Selection
    oCell.Formula = "=" & oCell.Value & "*2"
    Next oCell
    Set oCell = Nothing
    End Sub

    You can add bells and whistles if you like, such as error handling.

  5. #5
    New Lounger
    Join Date
    Nov 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Help - Adding std formula to cell contents (Office 2003/XP)

    Thanks - it works a treat! I still wonder why Excel defaults to replacing the active cell entirely instead of recording just the new keystrokes (when recording the macro).

    Thanks again.

    Andy

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

    Re: Macro Help - Adding std formula to cell contents (Office 2003/XP)

    The macro recorder in Excel does not record keystrokes but actions. In a Windows application that can be controlled by the mouse, by menus and by keyboard shortcuts, recording keystrokes is not very useful, and it would not result in easily maintainable code. The macro recorder does a reasonable job of creating VBA code, but there are situations in which the result is not what you want.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro Help - Adding std formula to cell contents (Office 2003/XP)

    Edited by HansV - placed <!t>[pre]<!/t> and <!t>[/pre]<!/t> tags around code to preserve indentation.

    Hi,

    This is a fancier version of Hans' macro with the multiplier able to be specified and a number of checks to ensure things like SUMs don't get the multiplier totalled.
    <pre>Sub ApplyMult()
    '
    ' ApplyMult Macro
    ' Increments Selected Cells or Column(s) by the supplied
    ' multiplier with 1.15 (115%) as the default.
    ' Note: SUM formulae excluded from function to avoid double incrementing
    ' Modified 27/11/02 with additional checks and options
    '
    Dim mult As Variant 'The multiplier value, user-defined
    Dim oCell As Range
    mult = Application.InputBox("Enter Multiplier:", _
    Title:="Selection Multiplier", Default:=1.15, Type:=1)
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    Dim oSelect As Range
    If TypeName(Selection) <> "Range" Then GoTo Finish
    Set oSelect = Intersect(Selection, ActiveSheet.UsedRange)
    For Each oCell In oSelect
    If Left(oCell.Formula, 1) = "=" Then
    If IsNumeric(oCell) Then
    If Left(oCell.Formula, 5) <> "=SUM(" Then
    If Left(oCell.Formula, 6) <> "=(SUM(" Then
    If Left(oCell.Formula, 10) <> "=SUBTOTAL(" Then
    If Left(oCell.Formula, 11) <> "=(SUBTOTAL(" Then
    oCell.Formula = "=(" & _
    Right(oCell.Formula, _
    Len(oCell.Formula) - 1) & ") * " & mult
    End If
    End If
    End If
    End If
    End If
    Else
    If oCell.Value <> "" Then
    If IsNumeric(oCell) Then
    oCell.Formula = "=(" & oCell.Value & ") *" & mult
    End If
    End If
    End If
    Next

    Finish:
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
    End Sub
    </pre>

    Good Luck!

    Peter Moran

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro Help - Adding std formula to cell contents (Office 2003/XP)

    Instead of all the checks you could wrap the entire expression like this (Excel 2000+ only):

    If oCell.HasFormula Then _
    oCell.Formula = Replace(oCell.Formula, "=", "=(", 1, 1, vbTextCompare) & ")*" & mult
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Help - Adding std formula to cell contents (Office 2003/XP)

    The I suggest you copy the column of data into a blank column, then do Hans' trick on that copied column. The you get to keep the source data, for later

Posting Permissions

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