Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Apply formula to Selected Cells (Excel 2K)

    Hi,

    How can I create a macro to increment the value of cells in a selection containing a number or formula by a fixed amount, say 1.15 (15%). Blank cells and cells containing text should be ignored.
    If the value of the cell is 1234 then after applying macro the cell should contain the formula

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Apply formula to Selected Cells (Excel 2K)

    Something like the following :

    Sub Multiply()
    Dim oCell As Range
    For Each oCell In Selection
    If Left(oCell.Formula, 1) = "=" Then
    oCell.Formula = "=(" & _
    Right(oCell.Formula, _
    Len(oCell.Formula) - 1) & ") * 1.15"
    Else
    oCell.Formula = "=(" & oCell.Formula & ") * 1.15"
    End If
    Next
    End Sub

    If your selection is A1:A3 and A3 = A1+A2, you could have problems as the multiplier would effectively be apllied twice to A3.

    Andrew C

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

    Re: Apply formula to Selected Cells (Excel 2K)

    Andrew,

    Thanks for your input.

    With some minor mods to handle text in the second part it is now looking good.

    <pre>Sub Apply115()
    Dim oCell As Range
    For Each oCell In Selection
    If Left(oCell.Formula, 1) = "=" Then
    oCell.Formula = "=(" & _
    Right(oCell.Formula, _
    Len(oCell.Formula) - 1) & ") * 1.15"
    Else
    If oCell.Value <> "" Then
    If IsNumeric(oCell) Then
    oCell.Formula = "=(" & oCell.Value & ") * 1.15"
    End If
    End If
    End If
    Next
    End Sub
    </pre>



    My new query is that it takes a little while (a couple of seconds) when a column is selected, which is the way the user tends to work. Is there any way this can be further minimised by restricting it to the used area of the column or the spreadsheet, or is this happening already?

    Thanks again and Regards,

    Peter Moran

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Apply formula to Selected Cells (Excel 2K)

    Try the following :<pre>Dim oCell As Range
    Dim oSelect As Range
    If TypeName(Selection) <> "Range" Then Exit Sub
    Set oSelect = Intersect(Selection, ActiveSheet.UsedRange)
    For Each oCell In oSelect
    '..etc
    </pre>

    I included a line to cater for selections that not appropriate.

    Andrew C

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

    Re: Apply formula to Selected Cells (Excel 2K)

    Andrew,

    Thanks again for your input.

    Will try your further suggestion and let you know result but it may not be today due to other commitments.

    Regards,

    Peter Moran

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Apply formula to Selected Cells (Excel 2K)

    Don't forget that you can do this with a paste special, multiple, so it is really just a one-liner. Here's a complete macro that prompts for everything and does error-checking. HTH --Sam
    <pre>Option Explicit
    Sub Macro1()
    Dim dblFactor As Double
    Dim rngSave As Range, rngM As Range, rngEmpty As Range
    Set rngSave = Selection ' Save selection
    Set rngEmpty = rngSave.SpecialCells(xlCellTypeLastCell).Offset(1)
    dblFactor = Application.InputBox(Prompt:="Enter factor", Type:=1)
    On Error GoTo noRange
    Set rngM = Application.InputBox(Prompt:="Select Range", _
    Default:=rngSave.Address, Type:=8)
    On Error GoTo 0
    rngEmpty = dblFactor
    rngEmpty.Copy
    rngM.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
    SkipBlanks:=False, Transpose:=False
    rngEmpty.Clear
    rngSave.Select ' Restore selection
    noRange:
    Set rngM = Nothing
    Set rngSave = Nothing
    Set rngEmpty = Nothing
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

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

    Re: Apply formula to Selected Cells (Excel 2K)

    Hi Sam,

    Thanks for your input.

    I noted a couple of points:

    1. Blank cells in the selected range end up as 0. You may have a suggestion as to how this can be resolved.

    2. The change to numbers does not leave a trace of what has happened. I like it where a number is wrapped with a formula so that the original can still be seen if required.

    3. Full marks for flexibility, like what is in ASAP Utilities, but my user works with a fixed factor and wants it as simple as possible.

    Regards,

    Peter Moran

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

    Re: Apply formula to Selected Cells (Excel 2K)

    Andrew,

    Managed to incorporate and test your changes and the result is spot on!!.

    Many thanks.

    Regards,

    Peter Moran

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Apply formula to Selected Cells (Excel 2K)

    Peter,

    It may not apply to your case, but not all formulas are numeric computations. For example, what if the formula is something like
    =CONCATENATE(...
    or
    =LEFT(...
    or any other string formula (or a call to a UDF that returns a string).

    Seems like the check for ISNUMERIC should come first and then test, if true, whether there's a formula in there.

    Fred

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

    Re: Apply formula to Selected Cells (Excel 2K)

    Fred,

    Thanks for your thoughts.

    While I think it is unlikely, I have now included your numeric test as suggested as good practice.

    Regards,

    Peter Moran

  11. #11
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Apply formula to Selected Cells (Excel 2K)

    Hi Peter,

    Two other things that you should consider, especially if your macro is going to be applied to large ranges:
    1) turn off screen updating (Application.ScreenUpdating = False); and
    20 set calculation to manual (Application.Calculation = xlManual),
    while the macro is running. Remember to re-set both at the end, though (Application.ScreenUpdating = False and Application.Calculation = xlAutomatic). These measures can markedly speed up your macro.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

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

    Re: Apply formula to Selected Cells (Excel 2K)

    Hi, Macropod,

    Thanks for your good thoughts. I have now included them both in my macro.

    Regards,

    Peter Moran

    Two heads are always better than one!

Posting Permissions

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