If two lists don't match

[Guest]

Excel Experts,

I would like my code to determine if two lists, which consists of text
entries in cells in two columns are the same. If they aren't the same, I'd
like to end the procedure.

Specifically, the two possible sets of lists is similar to the following:

SITUATION 1
A B C D
1 CurSym Price DataSym DataPrice
2 AMD 22 ADM 23
3 HWP 30 HWP 31
4 INTC 21 INTC 22
5 MSFT 27 MSFT 28


SITUATION 2
A B C D
1 CurSym Price DataSym DataPrice
2 AMD 22 ADM 23
3 HWP 30 IBM 80
4 INTC 21 INTC 22
5 MSFT 27 MSFT 28


I want my code to check if the symbols in column D - DataSym match those in
Column A - CurSym.
If there is any discrepancy (such as SITUATION 2, in which IBM is in column
D and not the HWP in column A) I want the procedure to end.

If the symbols in D-DataSym match those in A-Cursym, I want to copy the
prices in E-DataPrice over those in B-Price.

What is the most effective way to do this?

Should I create two range variables and then create a For..Next loops that
would compare the corresponding elements in each list?

Or, would it be possible to load each list of symbols into an array and then
check if the two arrays hold the same symbols in the same order?

Thanks,
Alan

 

[Guest]

Hi,

Try this:

Option Explicit

Sub MatchLists()

Dim lastrow As Long, r As Long

lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For r = 2 To lastrow
If UCase(Trim(Cells(r, "A"))) = UCase(Trim(Cells(r, "D"))) Then
Cells(r, "B") = Cells(r, "E")
Else
MsgBox "Mismatch in row " & r & " " & Cells(r, "A") & " " & Cells(r,
"D")
Exit Sub
End If
Next r

End Sub

HTH