Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VB to Return the Absolute Value (Excel 97 SR2 VB )

    Does anyone know the VB Property/Method that will return the absolute value of a given cell which can then be used in a formula.

    Ex.

    The value in a cell is -600 and I'm trying to test for values greater than 200. I want to change this value to 600 and then compare against my condition. I know that =ABS will do it in Excel but I would like to put it's equivalent into my existing code.

    By the way I would like to expressly thank Andrew and Legare for your help on my previous conditional formatting challenge. The process works beautifully!!

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

    Re: VB to Return the Absolute Value (Excel 97 SR2 VB )

    This works for me:

    <pre> If Abs(Worksheets("Sheet1").Range("A1")) > 200 Then
    MsgBox "It's greater than 200"
    Else
    MsgBox "It's less than or equal to 200."
    End If
    </pre>



    In a worksheet formula, this works for me:

    <pre>=IF(ABS(A1)>200,"Its Greater than 200","It's less than or equal to 200")
    </pre>

    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB to Return the Absolute Value (Excel 97 SR2 VB )

    Thanks Legare for your quick reply. Sorry if I appear dense but either I don't understand how to implement your suggestion or it's not quite what I was looking for. Here is my code:

    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
    Formula1:="200.00"
    Selection.FormatConditions(2).Interior.ColorIndex = 3

    Is there a Property that I can substitute for xlCellValue that will return the absolute value of the selection and then apply that to my condition? Thanks!!

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,939
    Thanks
    0
    Thanked 94 Times in 90 Posts

    Re: VB to Return the Absolute Value (Excel 97 SR2 VB )

    Hi,
    You could use:
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, _
    Formula1:="200.00", Formula2:="-200"
    Selection.FormatConditions(2).Interior.ColorIndex = 3
    Hope that helps.
    Regards,
    Rory
    Microsoft MVP - Excel.

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

    Re: VB to Return the Absolute Value (Excel 97 SR2 VB )

    Oh, you want to put it into conditional formatting. This should work:

    <pre> Selection.FormatConditions.Add Type:=xlExpression, _
    Formula1:="=ABS(" & Selection.Address & ") > 200"
    Selection.FormatConditions(2).Interior.ColorIndex = 3
    </pre>

    Legare Coleman

  6. #6
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB to Return the Absolute Value (Excel 97 SR2 VB )

    Thank you both Rory and Legare for your replies. I believe Legare remembers my situation and I should have added the remaining code. Rory, your code would work for me if this was the only condition I was testing but since I have a condition that is <100 there would be an overlap. Legare I will try out your suggestion. Thank you both!!

  7. #7
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB to Return the Absolute Value (Excel 97 SR2 VB )

    I'm sorry Legare but what does this mean

    " & Selection.Address & "

    My named range is "AR_Over_Short_Condtl". Would
    this be
    Formula1:=Abs("Selection.AR_Over_Short_Condtl") > 200 based on the code you suggested?
    Selection.AR_Over_Short_Condtl. Thanks!!

    Upon further effort I can't seem to pass the name range I'm using (AR_Over_Short_Condtl) with the function. If I put a cell address I can get the formula to work.

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

    Re: VB to Return the Absolute Value (Excel 97 SR2 VB )

    If AR_Over_Short_Condtl is the name of a single cell, then you would use:

    <pre> Formula1:="=ABS(AR_Over_Short_Condtl)>200"
    </pre>


    If AR_Over_Short_Condtl is a range of cells, then you will have to loop through those cells and use a different technique which I can show you if that is what you need.
    Legare Coleman

  9. #9
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB to Return the Absolute Value (Excel 97 SR2 VB )

    Yes, AR_Over_Short_Condtl is a range of cells. Here is the entire procedure that I use for this range:

    ' AR Over Short Conditional Formatting
    Application.Goto Reference:="AR_Over_Short_Condtl"
    Selection.FormatConditions.Delete

    ' AR Over Short Conditional Formatting (Yellow)
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
    Formula1:="100.01", Formula2:="200.00"
    Selection.FormatConditions(1).Interior.ColorIndex = 6

    ' AR Over Short Conditional Formatting (Red)
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
    Formula1:="Abs(AR_Over_Short_Condtl)> 200"
    Selection.FormatConditions(2).Interior.ColorIndex = 3

    ' AR Over Short Conditional Formatting (Green)
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
    Formula1:="100.00"
    Selection.FormatConditions(3).Interior.ColorIndex = 4

    I noticed another thread recently where some people were complaining about posters asking people for coding help. Believe me, I'm trying to look up and I hav e definitely learned alot from this forum with this exercise and I do appreciate the help you've provided!!

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

    Re: VB to Return the Absolute Value (Excel 97 SR2 VB )

    This is how I would do it:

    <pre>Dim oCell As Range
    ' AR Over Short Conditional Formatting
    Range("AR_Over_Short_Condtl").FormatConditions.Del ete

    For Each oCell In Range("AR_Over_Short_Condtl")
    ' AR Over Short Conditional Formatting (Yellow)
    oCell.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
    Formula1:="100.01", Formula2:="200.00"
    oCell.FormatConditions(1).Interior.ColorIndex = 6

    ' AR Over Short Conditional Formatting (Red)
    oCell.FormatConditions.Add Type:=xlExpression, _
    Formula1:="=Abs(" & oCell.Address & ")> 200"
    oCell.FormatConditions(2).Interior.ColorIndex = 3

    ' AR Over Short Conditional Formatting (Green)
    oCell.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
    Formula1:="100.00"
    oCell.FormatConditions(3).Interior.ColorIndex = 4
    Next oCell
    </pre>

    Legare Coleman

  11. #11
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB to Return the Absolute Value (Excel 97 SR2 VB )

    Legare,

    Thank you for your assistance. I follow the code except for the line below:

    Formula1:="=Abs(" & oCell.Address & ")> 200"

    Is & oCell.Address & susposed to inherit the range declared above (For Each oCell In Range("AR_Over_Short_Condtl") or was I expected to place something there in place of that statement. If I'm missing something very remedial I apologize. Thanks again!!

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

    Re: VB to Return the Absolute Value (Excel 97 SR2 VB )

    The For loop is going to place a different formula in the conditional format of each cell in the range. Each time through the loop, oCell is assigned to a different cell in the range. The .Address method returns the address of the cell as text. Therefore, if oCell is set to A1, oCell.Address returns $A$1 as a text string, and the formula that is put into the cell conditional format is:

    =Abs($A$1)>200

    You do not need to do anything but put the statement, as it appears, in your VBA program.

    The two equal signs are there because the first is required to assign the text string to Formula1 (actually that the the operator := not =), and the second must be in the conditional formula for it to be evaluated as a formula.
    Legare Coleman

  13. #13
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB to Return the Absolute Value (Excel 97 SR2 VB )

    Thank you for the explanation. I'm implementing your suggestion throughout my code where needed and thus far it's working great. I've learned so much with this exercise that it's aroused my hunger for more knowledge.

    What would be a good book that you would recommend for a beginner like myself that could get me on the road to being a resource to this forum like yourself? Thanks again!!

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

    Re: VB to Return the Absolute Value (Excel 97 SR2 VB )

    Woody's books are a pretty good place to start. Past that, I learned most of what I know on CompuServe before AOL bought it and started ruining it, and now here.
    Legare Coleman

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

    Re: VB to Return the Absolute Value (Excel 97 SR2 VB )

    I use and like Writing Excel Macros by Steven Roman. It is clear, concise, readable and affordable. --Sam
    <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>

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
  •