VBA Data validation with location of errors

[Godwin117]

I have this workbook that has columns A through E with information. For column A, I was looking for a VBA that will compare the current data in the cell (starts at A2) until the end of data, to see if it matches with data in ("Sheet2") range would be G2 until the end of data. This will go through each row until the end of the data in ("Sheet1"). If it doesn't match with a value from ("Sheet2"), it will populate with the row location in ("Sheet1") where it doesn't match in a message box. It can either stop at the first instance of it not matching or it can have all instances in one MSGBOX either way works. Thank you in advance for the assistance.

 

[Saurabhj]

Hi Godwin,

Please check below code:

Sub SearchValues()
    Dim lastRowSource As Integer, lastRowDestination, notAvail As String, check As Integer
    Application.ScreenUpdating = False
    
    lastRowSource = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    lastRowDestination = Sheets("Sheet2").Cells(Rows.Count, 7).End(xlUp).Row
    
    For rowno = 2 To lastRowSource
        check = 1
        For anotherRow = 2 To lastRowDestination
            If Sheets("Sheet1").Range("A" & rowno) = Sheets("Sheet2").Range("G" & anotherRow) Then
                check = 0
            End If
        Next
        If check = 1 Then notAvail = notAvail & " " & rowno
    Next
    
    MsgBox "Rowno in Sheet1 not found are:" & vbNewLine & notAvail
    Application.ScreenUpdating = True


End Sub

Thanks,
Saurabh

 

[Godwin117]

Hi Godwin,

Please check below code:

Sub SearchValues()
    Dim lastRowSource As Integer, lastRowDestination, notAvail As String, check As Integer
    Application.ScreenUpdating = False
   
    lastRowSource = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    lastRowDestination = Sheets("Sheet2").Cells(Rows.Count, 7).End(xlUp).Row
   
    For rowno = 2 To lastRowSource
        check = 1
        For anotherRow = 2 To lastRowDestination
            If Sheets("Sheet1").Range("A" & rowno) = Sheets("Sheet2").Range("G" & anotherRow) Then
                check = 0
            End If
        Next
        If check = 1 Then notAvail = notAvail & " " & rowno
    Next
   
    MsgBox "Rowno in Sheet1 not found are:" & vbNewLine & notAvail
    Application.ScreenUpdating = True


End Sub

Thanks,
Saurabh

Thank you Saurabh it worked perfectly, If I want to add it exits sub if none are found would that just be an if statement or how would I go about that

 

[Saurabhj]

Hi Godwin,

You can write Exit Sub but didn't understand the need of using this.

Thanks,
Saurabh

 

[Godwin117]

Hi Godwin,

You can write Exit Sub but didn't understand the need of using this.

Thanks,
Saurabh

I figured it out. Below is the updated code. The reason was because if everything matched, then I didn't want a message box, but everything works perfectly thanks again.

Sub Verification()
Dim lastRowSource As Integer, lastRowDestination, notAvail As String, check As Integer
Application.ScreenUpdating = False

lastRowSource = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lastRowDestination = Sheets("Sheet2").Cells(Rows.Count, 7).End(xlUp).Row

For rowno = 2 To lastRowSource
check = 1
For anotherRow = 2 To lastRowDestination
If Sheets("Sheet1").Range("A" & rowno) = Sheets("Sheet2").Range("G" & anotherRow) Then
check = 0
End If
Next
If check = 1 Then notAvail = notAvail & " " & "A" & rowno
Next
If notAvail = "" Then
Exit Sub
Else
MsgBox "Discrepancies found are:" & vbNewLine & notAvail
End If

    Application.ScreenUpdating = True


End Sub

 

[Godwin117]

I figured it out. Below is the updated code. The reason was because if everything matched, then I didn't want a message box, but everything works perfectly thanks again.

Sub Verification()
Dim lastRowSource As Integer, lastRowDestination, notAvail As String, check As Integer
Application.ScreenUpdating = False

lastRowSource = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lastRowDestination = Sheets("Sheet2").Cells(Rows.Count, 7).End(xlUp).Row

For rowno = 2 To lastRowSource
check = 1
For anotherRow = 2 To lastRowDestination
If Sheets("Sheet1").Range("A" & rowno) = Sheets("Sheet2").Range("G" & anotherRow) Then
check = 0
End If
Next
If check = 1 Then notAvail = notAvail & " " & "A" & rowno
Next
If notAvail = "" Then
Exit Sub
Else
MsgBox "Discrepancies found are:" & vbNewLine & notAvail
End If

    Application.ScreenUpdating = True


End Sub

One last request. Is there a way if Column B in Sheet1 is "Present" to verify with the same range used in Sheet2 otherwise skip verifying that row if Column B doesn't equal "Present".