How to Validate data in 2 columns

[sourabh_ajmera]

Hello everybody,

I am stuck on a small problem. Please help me out. I have tried couple of things as pasted in the code below.

I have to validate data in column A with data in column C
Each cell data in A should match its corresponding cell data in C. e.g: A1 = C1, A2 = C2....so on

If they dont match macro should give an error and stop the execution on user input choice.

I have the following formula in Column B "=IF(ISERROR(MATCH(A1,C1:C1,0)),"",A1)" which is draged down till Cell B100 (This tells me if the values are same or not)
Even when the values dont match, the macro doesnt give any error


Please refer the code below.

Sub Value_Check()

   Dim vLRow As Integer
    vLRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).row
     Dim rRng As Range
    For i = 1 To vLRow
    Range("B" & i).Select
    Set rRng = Sheets("Verify").Range("B" & i)
    If IsEmpty(rRng.Value) Then
             If MsgBox("Accounts dont match. Stop Macro?", vbYesNo) = vbYes Then Exit Sub
        Else
            ActiveCell.Offset(1, 0).Select
        End If
    Next i

End Sub

Please help me out even if its a small mistake as I am beginner. Thank you.

 

[RatExcel]

Sub Value_Check()

   Dim vLRow As Integer
    vLRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
    For i = 1 To vLRow
        If Cells(i, 1) <> Cells(i, 3) Then
            If MsgBox("Accounts dont match. Stop Macro?", vbYesNo) = vbYes Then Exit Sub
        Else
            Cells(i, 3).Select
        End If
    Next i

End Sub

 

[Russell Hauf]

I'd suggest just using conditional formatting or a simpler formula with a totals row above the data or next to the top-right of the data. Something like this:

cell B1: =(A1<>C1)+0

This will have a 1 if they are not equal, and a 0 if they are equal. Then you can sum column B and show that at the top somewhere, showing the user how many mismatches you have.

Now, that being said, if you really want to use what you have, maybe try:

If Sheets("Verify").Range("B" & i).Text = "" Then

(you can get rid of the rng Object since it doesn't look like you use it anywhere else)

Happy Excelling,

 

[Russell Hauf]

I'd suggest just using conditional formatting or a simpler formula with a totals row above the data or next to the top-right of the data. Something like this:

cell B1: =(A1<>C1)+0

This will have a 1 if they are not equal, and a 0 if they are equal. Then you can sum column B and show that at the top somewhere, showing the user how many mismatches you have.

Now, that being said, if you really want to use what you have, maybe try:

If Sheets("Verify").Range("B" & i).Text = "" Then

(you can get rid of the rng Object since it doesn't look like you use it anywhere else)

Happy Excelling,

My code above would be instead of:

If IsEmpty(rRng.Value) Then

 

[sourabh_ajmera]

Thank you @RatExcel and @Russell.
Both the methods worked perfectly fine. Cheers!