Match two rows using VBA

[Kartyk]

Hi All,

I am wokring on a VBA project to perform some recon. I have four columns that need matching.

Comparison is based on a match ID which indicates two rows goes together. So first check is this Match ID column. I have attached a file for reference.

* Compare the values of those two rows and check if they are matching, if yes, result should be Pass.
* IF not, then excel should highlight the cell in question and mark result as Fail.
* If the Type is Auto, then match only Counterparty name.
* If the Type is Manual, then match COunter party name, Curr Cross, Notional Amount etc...

Hope this is clear

[Kartyk]

Managed to get something goin ... feel this is tedious and not reliable ....Any suggestions welcome

Sub Matching()



Dim iLoop As Integer, Last_Row As Long, matchcol1 As Long, matchcol2 As Long, macthcol3 As Long
Dim matchcol4 As Long, matchcol5 As Long, mystring As String, matchcol6 As Long, matchcol7 As Long, matchcol8 As Long, matchcol9 As Long


Last_Row = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row

matchcol1 = Range("A1:K2").Find(What:="Match ID", LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False, SearchFormat:=False).Column
matchcol2 = Range("A1:K2").Find(What:="Counterparty Name", LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False, SearchFormat:=False).Column
matchcol3 = Range("A1:K2").Find(What:="Result", LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False, SearchFormat:=False).Column
matchcol4 = Range("A1:K2").Find(What:="Type", LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False, SearchFormat:=False).Column
matchcol5 = Range("A1:K2").Find(What:="Source", LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False, SearchFormat:=False).Column
matchcol6 = Range("A1:K2").Find(What:="Curr Cross", LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False, SearchFormat:=False).Column
matchcol7 = Range("A1:K2").Find(What:="Notional", LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False, SearchFormat:=False).Column
matchcol8 = Range("A1:K2").Find(What:="Amt. in Buy Curr", LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False, SearchFormat:=False).Column
matchcol9 = Range("A1:K2").Find(What:="Amount in Sell Curr", LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False, SearchFormat:=False).Column

Application.ScreenUpdating = False

With ActiveSheet

For iLoop = 2 To Last_Row
If .Cells(iLoop, matchcol1).Value = .Cells(iLoop + 1, matchcol1).Value And .Cells(iLoop, matchcol4).Value = "Manual" Then
If .Cells(iLoop + 1, matchcol2).Value = .Cells(iLoop, matchcol2).Value Or InStr(1, Sheet1.Cells(iLoop, matchcol2).Value, "LCH", vbTextCompare) <> 0 _
And .Cells(iLoop, matchcol6).Value = .Cells(iLoop + 1, matchcol6).Value _
And .Cells(iLoop, matchcol7).Value = .Cells(iLoop + 1, matchcol7).Value Or .Cells(iLoop, matchcol7).Value = .Cells(iLoop, matchcol8).Value _
Or .Cells(iLoop, matchcol7).Value = .Cells(iLoop, matchcol9).Value Then
.Cells(iLoop, matchcol3).Value = "Pass"
.Cells(iLoop + 1, matchcol3).Value = "Pass"
Else
.Cells(iLoop, matchcol3).Value = "Fail"
.Cells(iLoop + 1, matchcol3).Value = "Fail"

End If
End If

If .Cells(iLoop, matchcol1).Value = .Cells(iLoop + 1, matchcol1).Value And .Cells(iLoop, matchcol4).Value = "Auto" Then
If .Cells(iLoop + 1, matchcol2).Value = .Cells(iLoop, matchcol2).Value Or InStr(1, Sheet1.Cells(iLoop, matchcol2).Value, "LCH", vbTextCompare) <> 0 Then

.Cells(iLoop, matchcol3).Value = "Pass"
.Cells(iLoop + 1, matchcol3).Value = "Pass"
Else
.Cells(iLoop, matchcol3).Value = "Fail"
.Cells(iLoop + 1, matchcol3).Value = "Fail"



End If
End If
Next

End With
End Sub