View Single Post
  #11   Report Post  
Posted to microsoft.public.excel.programming
[email protected] seanryanie@yahoo.co.uk is offline
external usenet poster
 
Posts: 73
Default Compare 2 Lists and Take an Action Q

I've got some test data, and I've hit a speedbump, nothing matches, even when I deliberately edit to match an entry in Cols 4 & 7 to Cols 2 & 4.

I've edited the Column details from original, now matching D&G to N&P

Sub CompareList()
Dim LRowA As Long, LRowB As Long, i As Long, j As Long
Dim varA As Variant, varB As Variant

Sheets("Reconciliation").Select

With ActiveSheet
LRowA = .Cells(.rows.Count, "A").End(xlUp).Row
LRowB = .Cells(.rows.Count, "M").End(xlUp).Row
varA = .Range("A1:J" & LRowA)
varB = .Range("M1:P" & LRowB)

For i = 2 To LBound(varA) ' start in row 2 to allow for headers
For j = 2 To LBound(varB) ' start in row 2 to allow for headers
If varA(i, 4) & varA(i, 7) = varB(j, 2) & varB(j, 4) Then
..Cells(i, "K") = "x"
..Cells(j, "Q") = "x"

Exit For 'If there is only one match possible delete the apostroph before this line
End If
Next
Next
End With
End Sub