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.
'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
' insert data into cell Dcounter
ws3.cells(counter,4).value
counter = counter + 1
End If
Next j
j = 1
Next i
wbr Janos
WebTubbs,
That alpha character is not on the sampledata sheet to bring over.
sampledata sheet.
a b c
12 w 500
34 x 340
search criteria sheet
12 aa h
When you run the macro it searches for 12 and stuffs the row in results sheet.
results sheet
a b c
12 w 500
Result i need is
a b c d
12 w 500 h
i hope this clears it up.
JAYRU,
All of the experts here are volunteers from all over the world, and despite appearances, live normal lives outside of EE. I'm from Australia, and my last comment was made at 2:44am local time, and I needed some sleep, so my apologies for not being available until now.
Can I ask that you please be patient, and you will get the answer you need.
Now, to the problem at hand. The line I added....
ws3.Cells(counter, 4) = ws2.Cells(j, 3)
set the value of column D of the Results sheet to column C of the 'Search criteria' sheet, on the lookup row, as you asked. I tested it then with a small data set based on your brief example above, and the macro did indeed copy the value to column D. The result sheet looked like this....
a b c d
12 w 500 h
I'm not sure why it didn't work for you, but could you please post a bigger data set, or even better, post an example workbook?
Regards,
Wayne
Business Accounts
Answer for Membership
by: webtubbsPosted on 2007-10-10 at 08:46:19ID: 20049528
Hi JAYRU,
As it is at the moment, it's copying the entire row....
ws1.Rows(i).EntireRow.Copy
...so if the character is in col C in the 'Search criteria' sheet, it will be in col C of the 'Results' sheet.
Do you want it moved to column D, or in column C AND column D?
Regards,
Wayne