Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Avoiding duplicate edits (Excel 2000)

    Sub DEBITS()
    Range("DEBITS").Select
    For Each UnRoundedCell In Selection
    cellstring = UnRoundedCell.Formula
    If Left(cellstring, 1) = "=" Then
    cellstring = Right(cellstring, Len(cellstring) - 1)
    cellstring = "=round(" & cellstring & ",2)"
    UnRoundedCell.Formula = cellstring
    End If
    Next UnRoundedCell
    End Sub

    I have modified some existing code to test a range name "Debits" for formulae that do not have rounding functions and add the function if it doesn't. Now, if I run it a second time, I get Round (round( stuff. That could happen if an entry gets re-worked. I tried to say If (mid(cellstring,2) = "RO" then End If but without success. How to I ask it to skip the process if there alread is a rounding function present?

    I keep looking at these Macro books for gudiance, but they don't seem helpful. They are often trying to show you how to develop an interactive hot dog menu with pivot table pricing without getting to fundamentals that are generally applicable. Any practical book which is direct would be a welcome reference.

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

    Re: Avoiding duplicate edits (Excel 2000)

    You can test for the presence of "ROUND" in the formula:

    Sub DEBITS()
    Dim UnRoundedCell As Range
    Dim CellString As String
    For Each UnRoundedCell In Range("DEBITS")
    CellString = UnRoundedCell.Formula
    If Left(CellString, 1) = "=" Then
    If InStr(CellString, "ROUND") = 0 Then
    CellString = Mid(CellString, 2)
    CellString = "=ROUND(" & CellString & ",2)"
    UnRoundedCell.Formula = CellString
    End If
    End If
    Next UnRoundedCell
    End Sub

    Note 1: it is not necessary to select the range. I have omitted this from the code.
    Note 2: UnRoundedCell and CellString were not declared in your code. This might be because you wanted to keep the post short, or because they are module-wide or global variables. I sincerely hope it isn't because you don't have Option Explicit! (If it is, please turn on required variable declaration in Tools | Options...)

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding duplicate edits (Excel 2000)

    I didn't see that OptionExplicit in my originally copied code. I didn't quite get that unrounded property...it seemed a little strange that that would be a property. Please give me a reference or the statements I should have. Thanks for your comments!

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

    Re: Avoiding duplicate edits (Excel 2000)

    In the Visual Basic Editor, select Tools | Options... The Editor tab contains a check box labeled "Require Variable Declaration".

    If this check box is clear (off), you don't need to declare variables explicitly. You can just assign a value to a variable, for example

    MyValue = 37

    If you refer to a variable that has never been assigned a value, Visual Basic assumes that it has a default value (0 for numeric variables, "" for string variables). If you use a statement like this

    MsgBox "Value = " & (MyValie + 5)

    the result will be Value = 5, since you made a small mistake and used MyValie instead of MyValue. MyValie hadn't been assigned a value, so it is assumede to be 0. The result of the statement is different from what you had intended.

    If the "Require Variable Declaration" box is checked (on), you must declare each variable you use. for example

    Dim MyValue As Integer
    MyValue = 37

    The statement

    MsgBox "Value = " & (MyValie + 5)

    will now result in an error message, because MyValie has not been declared. So it is a good idea to turn on "Require Variable Declaration". This will insert a line Option Explicit at the top of each new module you create. It won't be inserted in existing modules automatically, but you can do that manually.

    In your code, you use variables UnRoundedCell and CellString. I added declarations for them at the beginning of the procedure.

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding duplicate edits (Excel 2000)

    Sub DEBITS()
    Range("DEBITS").Select
    For Each UnRoundedCell In Selection
    CellString = UnRoundedCell.Formula
    If InStr(CellString, "ROUND") = 0 Then
    Next UnRoundedCell
    End If
    CellString = "=round(" & CellString & ",2)"
    UnRoundedCell.Formula = CellString
    Next UnRoundedCell
    End Sub


    I get a block if statement error on this. I want to test if the cell has already been rounded. Why can't i say "if there is "ROUND" in the cell formula, go to the next cell in the selection? How do I say that? I want to loop through the cells in the Debit and later Credit columns to round 3-decimal values, and not just formulas, as the earlier-suggested code does.
    And a concept that has always stumped me: In testing for something like this, is the Primary condition the most "inside" if or the most "Outside" if in setting up multiple if tests? I can't seem to determine that directly for some reason. Which gets tested first?

    If 1
    ... If 2
    ......if 3
    ..........if 4
    this stuff confuses me in that I wonder which of the above is the first if to be tested. Thanks.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding duplicate edits (Excel 2000)

    You are getting the error because you have two Next statements to go with one For statement. You also can not put the next statment inside an If End If block if the For statement is not also inside the If block. I'm not quite sure what you mean by "Primary condition". The outermost condition is the most restrictive since the inner tests will not be performed if the outer test fails.

    I'm also not clear on what you are trying to do. Are you saying that you want to put in the ROUND function even if the cells contains a constant value, not only when it contains a formula? That does not make a lot of sense to me, but the following modification to Hans' code should do that:

    <pre>Option Explicit

    Sub DEBITS()
    Dim UnRoundedCell As Range
    Dim CellString As String
    For Each UnRoundedCell In Range("DEBITS")
    CellString = UnRoundedCell.Formula
    If InStr(CellString, "ROUND") = 0 Then
    If Left(CellString, 1) = "=" Then
    CellString = Mid(CellString, 2)
    End If
    CellString = "=ROUND(" & CellString & ",2)"
    UnRoundedCell.Formula = CellString
    End If
    Next UnRoundedCell
    End Sub
    </pre>

    Legare Coleman

  7. #7
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding duplicate edits (Excel 2000)

    Thank you. Oracle will not accept any value with more than 2 decimial places, so I have to round any values, as well as formulas (which are often just cell references to supporting schedule amounts) that have been copied into a debit or credit column that are nn.nnn or greater. It's easier to put an orbiter around Mars than to get this da*n stuff to work right.
    My question on the IFs was based upon trying to veiw them as one views nested IFs in formulas: the innermost one gets evaluated first, I think. That's what I was looking at.
    I tried this, based on reading the Dummies book,

    Sub TryAgain4()
    Dim Cellstring As String
    Dim UnroundedCell As Range
    Range("F12:F15").Select
    For Each UnroundedCell In Selection
    Cellstring = UnroundedCell.Formula
    If Left(Cellstring, 3) = "=(R" Then UnroundedCell.Formula = Cellstring

    ' Cellstring = Right(Cellstring, Len(Cellstring) - 1)
    Cellstring = "=round(" & Cellstring & ",2)"
    UnroundedCell.Formula = Cellstring
    ' End If
    Next UnroundedCell
    End Sub

    and it works the First time around, but the Second time around I get an Application defined or Object defined error stoppage on the UnroundedCell.Formula = cellstring line. Now why does that stop things? What I'm trying to say is "If it already has =(R i.e. it has the round function already done, then go to the next cell and check that one. I know that users will re-do all or part of the entry based on other facts, so this rounding function may need to be run more than once and I don't want =ROUND(=ROUND type stuff, which I have been getting.
    I will try your code, which I know is correct, and try and learn something. This is annoying, when one reads all these books, and I've read them a dozen times, and still cannot get something this simple to go.
    Thanks again.

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding duplicate edits (Excel 2000)

    Well, that code is checking for "=(R" to see if the round function is there. However, the code is inserting "=round" with no left parenthesis. That is why Hans used the INSTR function.
    Legare Coleman

  9. #9
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding duplicate edits (Excel 2000)

    OK Thanks...but what about values in the cells, not formulas? If there is an = test, the code will skip rounding 123.456 to 123.46, which I need done.
    Also, generally, how to you say " if something, do this, othewise, just keep going. In this code, what I was trying to say in part was " if you find it' s already got a round function, just keep going...don't touch it..." so I tried to have the Else say "next cell" but got mixed up... Right now, I'm trying to write code to insert border lines around colored cell backgrounds (instead of the blank "block look" you get if you don't insert 25% grey lines...) and I need to say "if Selection.Rows(count)>1, then do this stuff for the selection (Namely, add interior border rows) but how does one end it by saying otherwise (Else) just keep going?

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Avoiding duplicate edits (Excel 2000)

    In your for..next loop just have the IF.. then for a single line or di an if then.. endif. You do NOT have to have an else. If will ONLY do the things IF the statement is true and if NOT will just go to the NEXT in the loop

    Steve

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding duplicate edits (Excel 2000)

    Have you tried the last code that I posted? I believe that it does exactly what you are describing. If not could you describe what it is not doing that is not correct.

    In answer to your how to question:

    <pre> If Something Then
    DoThis
    End if
    </pre>


    Just leave out the Else if you don't need to do anything in the else case.
    Legare Coleman

  12. #12
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding duplicate edits (Excel 2000)

    Thanks. It works fine. I am not at all pleased that I cannot determine why my code below gets an Object-defined error at the last xCell.formula = cellstring. I don't undestand my logical problem.
    ANd also, please explain what the section with >>>If Left(CellString, 1) = "=" Then
    CellString = Mid(CellString, 2)
    End If
    I think that's what I'm missing. I can't seem to figue out why my Round test fails, why I cannot seem to tell it to skip the step when there's already a formula or value there. And as I said, I can't have 3-decimal values, either, so that's why this has to cover both formulas and values, as yours does. Thanks for the help. Maybe if I buy a thicker book and use it as a pillow, that would help.

    Sub TryAgain4()
    Dim CellString As String
    Dim XCell As Range
    Range("F12:F15").Select
    For Each XCell In Selection
    CellString = XCell.Formula
    If Left(CellString, 3) = "=R" Then
    XCell.Formula = CellString
    End If
    CellString = "=Round(" & CellString & ",2)"
    XCell.Formula = CellString
    Next XCell
    End sub

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Avoiding duplicate edits (Excel 2000)

    That code removes the equal sign from any formula you have before putting it in the "round function"

    If you have in cell A1
    =round(a10,2) you don't have to change it
    If it is a value (eg 15.345 you will convert to
    =round(15.345,2)
    BUT in your current code, if the contents are a formula:
    eg =A10, you TRY to make the formula:
    =round(=a10,2)
    whihc gives you a RUNTIME error since it is NOT a valid formula.
    If the first character = "=" then you remove it with the mid statement:
    =a10 becomes A10 and then the formula becomes:
    =round(a10,2)

    Steve

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding duplicate edits (Excel 2000)

    Your code has a couple of problems. First, as Steve said, if the cell contains a formula but not Round, then you leave the equal sign on the formula and add the Round. Second, if the cell starts with "=R" (a very dangerous test since the cell could contain =R1), you put the unaltered formula back in the cell, but then the code adds another Round function to the one that is already there and puts that into the cell. Your code would need to be modified to something like this to work (untested):

    <pre>Option Explicit

    Sub TryAgain4()
    Dim CellString As String
    Dim XCell As Range
    Range("F12:F15").Select
    For Each XCell In Selection
    CellString = XCell.Formula
    If Left(CellString, 3) = "=R" Then
    XCell.Formula = CellString
    Else
    If Left(CellString, 1) = "=" Then
    CellString = Right(CellString, Len(CellString) - 1)
    End If
    CellString = "=Round(" & CellString & ",2)"
    XCell.Formula = CellString
    End If
    Next XCell
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

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