VB Code to look for certain data and bring related data

[rizmomin]

Hello:
Please refer to attached file.
I have downloaded Bank Statement and data are shown in column A thru G.
I will have Payroll Data in column O thru T.

I need VB Code to go thru each check # in column C and find the name of the Payee from column R for correpsoning check # in column O.
Will need to confirm check # in column C and amount in column G with Check # in column O and Amount in column S.
Once name of the payee is found then enter in column D and add "Payroll:Net" in column E as shown in yellow highlighted.


Let me know if you have any questions.
Thanks.

Riz

[Logit]

.
The checks values match EXCEPT most of them have a NEGATIVE sign in front (Col G). Does the NEGATIVE sign need to remain or are you intending to remove the NEGATIVE sign first then
proceed with the matching ?

[Logit]

.
If changing the numbers in Col G to positive is acceptable, this macro will accomplish the goal :

Please Login or Register  to view this content.

[Marc L]

According to the attachment as it is, a VBA starter :

Sub Demo1()
    Dim V, R&, Rf As Range
        V = Application.Index([A1].CurrentRegion, Evaluate("ROW(1:" & [C1].End(xlDown).Row & ")"), [{3,7}])
        Application.ScreenUpdating = False
    With Range("O2", [O1].End(xlDown))
        For R = 2 To UBound(V)
               Set Rf = .Find(V(R, 1), , , xlWhole)
            If Not Rf Is Nothing Then
                Cells(R, 4) = Rf(1, 4).Text
                If Abs(V(R, 2)) = Rf(1, 5).Value2 Then Cells(R, 5) = "Payroll:Net"
            End If
        Next
    End With
        Application.ScreenUpdating = True
        Set Rf = Nothing
End Sub 

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[rizmomin]

Hello:

Marc L : I will be using your code, i see small problem.
Please refer to attached file, Sheet2.
This is the result after running your code.
Works with small issue.

Need to verify check# and Amount

Look at the highlighted one.
170.3 compared with 171.3 which does not match so in this case, do not change..

Would appreciate if you can help modify to code to accomplish this.


Let me know if you have any questions.
Thanks.

R

[Marc L]

Not an issue from my code as it runs as expected according to the original explanation & attachment
and as you can easily check yourself following these directions :

• Copy cell E20 to E21 or just make blank cell E21
  
  
• Execute my demonstration …

[rizmomin]

Hello Marc L

I see it, Thanks

Riz