The script way below labeled Previous question works great from a previous question i had. Now i want the "for loop" to check for records that don't match rather than the ones that do. I cannot get it to work correctly. I changed the for loop to read this:
__________________________
______
For i = 1 To RowsA
For j = 1 To RowsB
If ws1.Cells(i, 1) <> ws2.Cells(j, 1) Then
ws2.Rows(i).EntireRow.Copy
ws3.Cells(counter, 1)
counter = counter + 1
__________________________
_____
Changing the for loop does pull the right information over to ws3 but multiple times. All i want it to do is when it does the search and does not find a match to put the information from ws2 to ws3 for that row one time.
Previous question:
I'm not sure exactly how to ask this question but i'll give it a try. Below is a script to search a sheet using criteria on another sheet and then putting the result set on a third sheet. All works great thanks to the help of some EE people. However i need to add something and can't figure it out. On the search criteria sheet in column "c" is a alpha character. its either B,H, OR N. When the macro runs and finds a row that meets its search criteria i need the alpha character that was in column "c" of the searchcriteria sheet to be put in a fourth column on the results sheet coresponding to the match it found.
Sub CompareSheets2()
Dim ws1, ws2 As Worksheet
Dim RowsA, i, counter As Long
Set ws1 = Sheets("SampleData") ' your sample data sheet
Set ws2 = Sheets("Search criteria") ' your sheetname
Set ws3 = Sheets("Results") ' your sheetname
Application.ScreenUpdating
= False
Application.Calculation = xlCalculationManual
RowsA = ws1.Cells(Rows.Count, 1).End(xlUp).Row
RowsB = ws2.Cells(Rows.Count, 1).End(xlUp).Row
counter = 1
'copy values
For i = 1 To RowsA
For j = 1 To RowsB
If ws1.Cells(i, 1) = ws2.Cells(j, 1) Then
ws1.Rows(i).EntireRow.Copy
ws3.Cells(counter, 1)
counter = counter + 1
End If
Next j
j = 1
Next i
Application.ScreenUpdating
= True
Application.Calculation = xlCalculationAutomatic
End Sub
