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!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450
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>
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,297
Messages
5,836,479
Members
430,434
Latest member
whatabout

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top