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

Topic

New Reply

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!!

Reply | Quote

Replies

This works for me:

    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

In a worksheet formula, this works for me:

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

Reply | Quote

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!!

Reply | Quote

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.

Reply | Quote

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

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

Reply | Quote

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!!

Reply | Quote

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.

Reply | Quote

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

    Formula1:="=ABS(AR_Over_Short_Condtl)>200"

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.

Reply | Quote

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!!

Reply | Quote

This is how I would do it:

Dim oCell As Range
' AR Over Short Conditional Formatting
    Range("AR_Over_Short_Condtl").FormatConditions.Delete

    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

Reply | Quote

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!!

Reply | Quote

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.

Reply | Quote

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!!

Reply | Quote

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.

Reply | Quote

I use and like Writing Excel Macros by Steven Roman. It is clear, concise, readable and affordable. –Sam

Reply | Quote

Legare,

How would I change this to an absolute value expression:

‘ 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

I need to test for the absolute values between 100.01 but less than 200. I tried :

oCell.FormatConditions.Add Type:=xlExpression, _
Formula1:=”=Abs(” & oCell.Address & “)>200.01″
‘Formula2:=”=Abs(” & oCell.Address & “)<= 300.00"

This worked but it would not look at the data with both conditions evaluated at the same time like it did when I used xlBetween. Any suggestions?

Thank you.

Reply | Quote

Any ideas why this won’t work?

‘ Cash Over Short Condtional Formatting (Yellow)
oCell.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:=”=Abs(” & oCell.Address & “)>200.01″, _
Formula2:=”=Abs(” & oCell.Address & “)<= 300.00"
oCell.FormatConditions(1).Interior.ColorIndex = 6

Reply | Quote

Hi,
You could try:
With oCell
.FormatConditions.Add Type:=xlExpression, _
Formula1:=”=and(Abs(” & .Address & “)>200.01,abs(” & .Address & “)<=300)"
.FormatConditions(1).Interior.ColorIndex = 6
End With
Hope that helps.

Reply | Quote

Thanks Rory!! Do you know how long I’ve been looking for a way to test for an amount between two points with a formula. It’s easy to do via conditional formatting but to simulate that with an expression has been frustrating to say the least.

It works like a charm!! Thanks again.

Reply | Quote