Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts

    Macro to remove values in formulas

    From time to time I need to amend formulas manually by by adding or subtracting a value when making adjustments

    At the beginning of each year, I need to manually remove all the values the appear directly at the end of the formula on all sheets except the first and last sheet

    for examples -see below

    =+F208/2-2000 -I need to manually remove -2000)

    IF(G191="Left",0,IF(G197="",0,Commission9(G197+G19 5)))-2500 -I need to remove - 2500


    Your assistance in resolving this is most appreciated

    I have also posted on MrExcel.com and Ozgrid, but as this may be difficult, I have not received any replies

    http://www.mrexcel.com/forum/excel-q...r-formula.html


    http://www.ozgrid.com/forum/showthread.php?t=194262

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I would never trust a spreadsheet which had been subjected to the sort of mechanical adjustment you are seeking.

    It is not too hard to write the sort of macro you have in mind, but a wise person wouldn't.

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Martin

    Thanks for the advice

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Howard,

    What I'd suggest:
    1. Create a Variables Sheet (or what ever you want to name it)
    2. Have two columns: Name and Value
    3. Fillin all of your variable items naming the Value column cells with the Name in the first column.
    4. Now adjust all your formulas to used the Name of the value.


    You're now ready for any change without ever having to change formulas. IMHO it is ALWAYS a bad idea to hardcode values into formulas if you can help it. This all harkens back to my days doing payroll programming in PL/1. The federal gov't had for decades used 2080 as the number of hours in a year. Then some bright elected official decided that it should be 2087 because every 11 years there was an extra 2 week pay period to even out the calendar. Needless to say that 2080 was hard coded everywhere and it took quite a while to ferret them all out. I learned my lesson!

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    You aren't too clear about the criteria but this should do as you like.
    Code:
    Option Explicit
    Sub fixformulas()
    Dim sh As Worksheet
    Dim f As Range
    Dim mf As String
    For Each sh In Worksheets
    If sh.Index > 1 And sh.Index < Sheets.Count Then
     For Each f In sh.Cells.SpecialCells(xlCellTypeFormulas)
      mf = f.Formula
      If Right(mf, 5) = "-2000" Or Right(mf, 5) = "-2500" Then
       f = Left(mf, Len(mf) - 5)
      End If
     Next f
    End If
    Next sh
    End Sub
    Last edited by RetiredGeek; 2015-04-19 at 09:57. Reason: Added Code Tags
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi RG

    Thanks for your input. I like your idea very much and will implement this

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Don

    Thanks for your code. What I was looking for was to remove all values in my formulas for eg +B205/2-2000, =IF(B182<0,0,Commission8(B182+B181))+7250 etc

    I like RG's idea of not hardcoding values into formulas if one can help it

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Howard

    Only an accountant would need something like this!

    I modified Don's code to make it 'remove' non-specific values, and mixed 'added values' at the end of a formula:
    Code:
    for example:
    =SUM(D2:D6) - 2500-56.3+1200 >> =SUM(D2:D6)
    
    =D14+E14-1000 >> =D14+E14
    
    =D11+E11 >> no change
    
    =SUM(D7:D11) - 2500 >> =SUM(D7:D11)
    Here is the code:
    Code:
    Sub fixformulas()
    
    Dim sh As Worksheet
    Dim f As Range
    Dim mf As String
    
    For Each sh In Worksheets
    If sh.Index > 1 And sh.Index < Sheets.Count Then
    
    For Each f In sh.Cells.SpecialCells(xlCellTypeFormulas)
    mf = f.Formula
    
    zUpdate = False
    
    zMinusPosition = InStrRev(mf, "-")
    zPlusPosition = InStrRev(mf, "+")
    zPosition = Application.Max(zMinusPosition, zPlusPosition)
    
    Do Until zPosition = 0
    If zPosition > 0 Then
    zValue = Mid(mf, zPosition)
    zTrimValue = Replace(zValue, " ", "")
    If zTrimValue Like "?#*" Then               '<< ignores +E27 etc
    mf = Trim(Replace(mf, zValue, ""))
    zUpdate = True
    zMinusPosition = InStrRev(mf, "-")
    zPlusPosition = InStrRev(mf, "+")
    zPosition = Application.Max(zMinusPosition, zPlusPosition)
    Else
    zPosition = 0
    End If
    End If
    Loop
    
    
    If zUpdate = True Then
    f = mf
    End If
    
    Next f
    End If
    Next sh
    
    End Sub
    I have attached an example file where you can practice entering formulas, and then see how they get 'fixed'.

    zeddy
    Attached Files Attached Files

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Martin

    Re your post#2
    OK, I failed your 'wise' test. But I found the challenge harder than anticipated!
    And, as someone who loves z's, can you put your Location as Cuzco????

    zeddy

  10. #10
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Yeah OK Zeddy, although I shall be in Ponteland tomorrow (my sister lives there, and isn't well) so perhaps I should change it to Darraz Hall ?

    I do worry about Howard's spreadsheets - there are so many interlinkings, patches, workarounds, borrowed bits of code and data questions that I wonder whether it is possible to know that they are giving reliable outputs.

  11. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Many times I create a hidden sheet called "Settings" that is home to variables and constants. I fully agree with RG and as he indicates, listing the name and value would be required. But I would also create a third column indicating the cell(s) that use the formula for referencing. An alternate way is to place the variables on the "Settings" sheet in the exact cell they correspond to on the "Main" sheet and add comments for additional notes.

    Maud

  12. #12
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Thanks for your code Zeddy. If I need your code to ignore last two sheets, do I amend as follows?

    Code:
     If sh.Index > 1 And sh.Index < Sheets.Count -1 Then

  13. #13
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Maud

    Great idea to add 3rd column referencing the Cell/s that use the formula for referencing

  14. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Howard

    Yes, that amendment would do the trick. You're getting the hang of it.

    zeddy

  15. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Maud

    I agree with you all.
    I don't know exactly why Howard adds bits on the end of his formulas, but in my own personal bill-paying records I've sometimes entered in an 'Amount Paid' cell something quick like =17250+6000+N("credit note for 6000")
    I suppose I could have just entered 23250 and used a cell note!!

    But as far as solving the question posed, it was trickier than I originally thought to 'remove' stuff from the end of formulas as posed.
    I doubt whether that code will be used very often by others!

    zeddy
    Last edited by zeddy; 2015-04-20 at 06:35.

Page 1 of 2 12 LastLast

Posting Permissions

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