VBA Data validation with location of errors

Godwin117

Board Regular
Joined
Dec 19, 2019
Messages
55
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
390
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi Godwin,

Please check below code:

VBA 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

Board Regular
Joined
Dec 19, 2019
Messages
55
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi Godwin,

Please check below code:

VBA 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

Active Member
Joined
Jun 6, 2020
Messages
390
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi Godwin,

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

Thanks,
Saurabh
 

Godwin117

Board Regular
Joined
Dec 19, 2019
Messages
55
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.

VBA Code:
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

Board Regular
Joined
Dec 19, 2019
Messages
55
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.

VBA Code:
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".
 

Watch MrExcel Video

Forum statistics

Threads
1,122,355
Messages
5,595,680
Members
414,008
Latest member
SNesbyCarr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top