nested loop ; object or number ; find function

[ArizonaEmployee]

Hello,
Thank you for your help in advance.
I've got two things I'm specifically looking for help on, and also appreciate any recommendations.

1. I'm having trouble getting the .find function working below - which is bold and underlined​

2. I'm wondering if the 'IF' statment below the .find function (underlined) should be written as MatchRow.offset(x,x).value = [xx]​

Definitely open for recommendations, as I feel there is a better way to achieve the objective; the objective is to find rows with "UE" and no color (-4142), then use the dollar amounts to see if there are any offsetting amounts in the same data set with a few differences ("AB", etc). If found, then highlight both values pink (26).

   Dim TCfind As Currency
    Dim LCfind As Currency
    Dim GCfind As Currency
    Dim CurrentPC As String
    Dim Loop13Row As Long
    
    Dim CurrentRow As Integer
    Dim CurrentRowSelect As Range
    
    Dim MatchRow As Object
    Dim MatchRowSelect As Object
    
    Dim g As Integer
    Dim Matches As Integer
        
        Loop13Row = LastRowGJLI
        
        i = 2
        
        For i = 2 To Loop13Row
        
        g = 1
        Range("L" & [i]).Activate
        
        If ActiveCell.Value = "UE" And ActiveCell.Interior.ColorIndex = -4142 Then
                TCfind = ActiveCell.Offset(0, 8)*-1
                LCfind = ActiveCell.Offset(0, 10)*-1
                GCfind = ActiveCell.Offset(0, 12)*-1
                CurrentPC = ActiveCell.Offset(0, -6)
                CurrentRow = ActiveCell.Row
                Set CurrentRowSelect = Cells(CurrentRow, "A")
        
            Matches = Application.WorksheetFunction.CountIf(Range("T2", "T" & LastRowGJLI), [TCfind])
            If Matches > 0 Then
                For g = 1 To Matches
               [B][U]Set MatchRow = Range("T" & i, "T" & LastRowGJLI).Find(What:=[TCfind])
[/U][/B]         [U] If Cells(MatchRow, "F") = CurrentPC And _
                    Cells(MatchRow, "V") = LCfind And _
                    Cells(MatchRow, "X") = GCfind And _
                    Cells(MatchRow, "L") = "AB" Then[/U]
                    CurrentRowSelect.EntireRow.Interior.ColorIndex = 26
                    MatchRow.EntireRow.Interior.ColorIndex = 26
                    End If
                Next g
            End If
        End If
        Next i

Thank you much!

 

[AlphaFrog]

Definitely open for recommendations, as I feel there is a better way to achieve the objective; the objective is to find rows with "UE" and no color (-4142), then use the dollar amounts to see if there are any offsetting amounts in the same data set with a few differences ("AB", etc). If found, then highlight both values pink (26).

Can you better describe your data configuration? Examples could help.
What's the "same Data Set"?
Why do you multiply TCFind (and the others) by -1; TCfind = ActiveCell.Offset(0, 8)*-1 ?

 

[ArizonaEmployee]

Hi, thank you for the questions - it's quite difficult for me to make it clear without being able to post a workbook, but here is a try.

The data has many columns each describing finanical transactions and I am trying to analyze the data set; this particular step is trying to find transactions with the same dollar amounts with one being positive and one being negative - I find the positive and *-1 in the variable I search for. In each rows' columns there is "doc type , TC,LC, and GC" and I am trying to find where a row with a "doc type" of "UE" (is positive) that offsets another row with a "doc type" of "AB" (is negative). Here is a limited example:

[TABLE="width: 1039"]
<TBODY>[TR]
[TD]Column (L)
Doc. Type</SPAN>[/TD]
[TD]
RI</SPAN>[/TD]
[TD]
Text</SPAN>[/TD]
[TD]
User name</SPAN>[/TD]
[TD]
Doc. Date</SPAN>[/TD]
[TD]
Entry Date</SPAN>[/TD]
[TD]
Pstng Date</SPAN>[/TD]
[TD]
Value date</SPAN>[/TD]
[TD] (T)
TC[/TD]
[TD]Crcy</SPAN>[/TD]
[TD] LC[/TD]
[/TR]
[TR]
[TD]AB</SPAN>[/TD]
[TD]NB</SPAN>[/TD]
[TD]RGJVREC8</SPAN>[/TD]
[TD]FCOPERUP9</SPAN>[/TD]
[TD]38988</SPAN>[/TD]
[TD]40638</SPAN>[/TD]
[TD]38990</SPAN>[/TD]
[TD]38990</SPAN>[/TD]
[TD]-1275</SPAN>[/TD]
[TD]USD</SPAN>[/TD]
[TD]-1371</SPAN>[/TD]
[/TR]
[TR]
[TD]UE</SPAN>[/TD]
[TD]NB</SPAN>[/TD]
[TD]RGJVREC8</SPAN>[/TD]
[TD]FCOPERUP9</SPAN>[/TD]
[TD]38988</SPAN>[/TD]
[TD]40638</SPAN>[/TD]
[TD]38990</SPAN>[/TD]
[TD]38990</SPAN>[/TD]
[TD]0.39</SPAN>[/TD]
[TD]USD</SPAN>[/TD]
[TD]0.39</SPAN>[/TD]
[/TR]
[TR]
[TD]AB</SPAN>[/TD]
[TD]NB</SPAN>[/TD]
[TD]RGJVREC8</SPAN>[/TD]
[TD]FCOPERUP9</SPAN>[/TD]
[TD]38988</SPAN>[/TD]
[TD]40638</SPAN>[/TD]
[TD]38990</SPAN>[/TD]
[TD]38990</SPAN>[/TD]
[TD]1274.61</SPAN>[/TD]
[TD]USD</SPAN>[/TD]
[TD]1274.61</SPAN>[/TD]
[/TR]
[TR]
[TD]UE</SPAN>[/TD]
[TD]NB</SPAN>[/TD]
[TD]RGJVREC8</SPAN>[/TD]
[TD]FCOPERUP9</SPAN>[/TD]
[TD]39015</SPAN>[/TD]
[TD]40638</SPAN>[/TD]
[TD]39021</SPAN>[/TD]
[TD]39021</SPAN>[/TD]
[TD]1275</SPAN>[/TD]
[TD]USD</SPAN>[/TD]
[TD]1371</SPAN>[/TD]
[/TR]
</TBODY>[/TABLE]