nested loop ; object or number ; find function

ArizonaEmployee

New Member
Joined
Sep 16, 2014
Messages
2
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).
Code:
   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!
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,278
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

New Member
Joined
Sep 16, 2014
Messages
2
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:

Column (L)
Doc. Type
</SPAN>

RI
</SPAN>

Text
</SPAN>

User name
</SPAN>

Doc. Date
</SPAN>

Entry Date
</SPAN>

Pstng Date
</SPAN>

Value date
</SPAN>
(T)
TC
Crcy</SPAN> LC
AB</SPAN>NB</SPAN>RGJVREC8</SPAN>FCOPERUP9</SPAN>38988</SPAN>40638</SPAN>38990</SPAN>38990</SPAN>-1275</SPAN>USD</SPAN>-1371</SPAN>
UE</SPAN>NB</SPAN>RGJVREC8</SPAN>FCOPERUP9</SPAN>38988</SPAN>40638</SPAN>38990</SPAN>38990</SPAN>0.39</SPAN>USD</SPAN>0.39</SPAN>
AB</SPAN>NB</SPAN>RGJVREC8</SPAN>FCOPERUP9</SPAN>38988</SPAN>40638</SPAN>38990</SPAN>38990</SPAN>1274.61</SPAN>USD</SPAN>1274.61</SPAN>
UE</SPAN>NB</SPAN>RGJVREC8</SPAN>FCOPERUP9</SPAN>39015</SPAN>40638</SPAN>39021</SPAN>39021</SPAN>1275</SPAN>USD</SPAN>1371</SPAN>

<TBODY>
</TBODY>
 

Watch MrExcel Video

Forum statistics

Threads
1,109,320
Messages
5,527,996
Members
409,798
Latest member
Snake68

This Week's Hot Topics

Top