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
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
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
counter = counter + 1
End If
Next j
j = 1
Next i
Application.ScreenUpdating
Application.Calculation = xlCalculationAutomatic
End Sub
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
I have done that as well and it does bring the right data but multiple times. Wont be on till later tonight thanks for helping me....
Here is sample data that is in ws1:
A B C D
1 CAT 1000 2000
2 DOG 3000 4000
3 BIRD 5000 6000
__________________________
here is search data on ws2
9 beaver bear
11 log branch
__________________________
Here is the result in ws3
9 beaver bear
11 log branch
9 beaver bear
11 log branch
9 beaver bear
11 log branch
9 beaver bear
11 log branc
__________________________
Result should be:
9 beaver bear
11 log branch
Hope this helps. That was using this code:
For i = 1 To RowsA
For j = 1 To RowsB
If ws1.Cells(i, 1) <> ws2.Cells(j, 1) Then
ws2.Rows(j).EntireRow.Copy
counter = counter + 1
End If
Next j
j = 1
Next i
Application.ScreenUpdating
Application.Calculation = xlCalculationAutomatic
can we do it the other way round
Dim blnFound as Boolean
blnFound = False
For j = 1 To RowsB
For i = 1 To RowsA
If ws1.Cells(i, 1) = ws2.Cells(j, 1) Then
blnFound = True
Exit For
End If
Next i
If not blnFound then
ws2.Rows(j).EntireRow.Copy
counter = counter + 1
End If
blnFound = False
Next j
Spattewar,
We are almost there. Appears as tho when i use simple data to test it works okay using your last suggestion. However when i use the live data which is about 5000 records and about 4000 records to match against the results come back as none of the 4000 records matching. The result should be only about 200 at the most not matching. Not sure if is a data formatting problem or not. I have pasted values only and formatted as text and even tried formatting as number and get same result. Gonna do some more testing. I believe your macro is working but am a loss of why when using live data it does not. If you have any ideas let me know. I will award points after just a little more testing.
Business Accounts
Answer for Membership
by: spattewarPosted on 2008-01-16 at 12:34:52ID: 20675795
can you try this
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
counter = counter + 1
Exit For
End If
Next j
j = 1
Next i