How to match positive and negative values within subgroups

< Back to Search results

 Category: [Excel]  Demo Available 

How to match positive and negative values within subgroups




The user has a sheet which contains credits and debits for several accounts:

Customer Amount
ABC 2000
XYZ 3000
XYZ 1000
ABC 1000
ABC -5000
XYZ 1000

The user needs to match negative and positive values within each account and highlight them.




Sub x()
Dim rFind As Range, sAddr As String, r As Range, s() As String, i As Long, a, b
With Range("A2", Range("A" & Rows.Count).End(xlUp))
    ReDim s(1 To 2 * .Count)
    For Each r In .Cells
        Set rFind = .Find(What:=r, After:=r, LookAt:=xlWhole, searchdirection:=xlNext, _
                          MatchCase:=False, SearchFormat:=False)
        If Not rFind Is Nothing Then
            sAddr = rFind.Address
                If r.Offset(, 1) = -1 * rFind.Offset(, 1) Then
                    a = Application.Match(r.Address, Application.Index(s, 1, 0), 0)
                    b = Application.Match(rFind.Address, Application.Index(s, 1, 0), 0)
                    If Not IsNumeric(a) And Not IsNumeric(b) Then
                            r.Resize(, 2).Interior.Color = vbYellow
                            rFind.Resize(, 2).Interior.Color = vbYellow
                             i = i + 1
                            s(i) = r.Address
                            s(i + 1) = rFind.Address
                            i = i + 1
                            Exit Do
                    End If
                End If
                Set rFind = .FindNext(rFind)
            Loop While rFind.Address <> sAddr And rFind.Address <> r.Address
        End If
    Set rFind = Nothing
    sAddr = ""
    Next r
End With
End Sub


Obtained from the OzGrid Help Forum.

Solution provided by StephenR.


See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:

How to sort values in positive and negative numbers with formula
How to average both positive and negative numbers


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.





stars (0 Reviews)