VBA needed to extract data from specific cells in different worksheet

Hello. I am new to this, and trying to figure out a VBA code that will work for my set of data. I have attached a much smaller version of the data I am working with.

I am trying to make a 'tool' sheet in Excel that can search all of the data from the 'raw data' sheet and return specific cells from that 'raw data' sheet. Specifically, in the attached workbook, I want users to be able to enter values into the yellow highlighted cells (Test #s and Class rank) on the 'tool' sheet, press the "Go" button, and from there I need the VBA to:

1. Go to the 'raw data' sheet and first search for the row in Column A that matches the Test # given in the yellow highlighted cell. Secondly, if the correct test # is found, I need the code to also search in Column B for the class to also match the value that was entered in the yellow highlighted cell on the 'tool' sheet.

2. Once both columns match, I want the VBA code to go over to column E and to copy the cell directly across from the first row that has both correct values, and also to copy the second cell. For example, if test #1 and freshman were entered in the yellow cells, the code would find that row 2 matches the requirements, so it would go over and copy E2 and E3 on the sheet.

3. Lastly, I want the code to go back and paste the 2 copied cells in the specified green highlighted cells on the 'tool sheet'.

4. In the case that the user enters something that does not exist, such as "senior" in the yellow class cell, I also need to code to come back and say something like "invalid entry".

Thanks so much for any help you can give!!!!!
Mindy

Thanks so much jaslake. This works perfectly! I have another question for you. I have a larger data set that I am essentially doing the same thing with. I ended up combining 2 cells in column A of the data sheet so that I only had to find one value in the code. Below is my code (very basic!). It works correctly, but it always goes to the last row (#2861) even when it finds what it is looking for much earlier in the loop. Also, when it is finished, I am looking at the bottom of the sheet, instead of at the top of the "datatool" sheet. Do you know what I can enter into the code to fix these 2 problems? Also, any easy suggestions to make this code a little better (i.e. naming variables, using range instead of cells, etc.)?

Sub Button3_Click()

Sheets("DataTool").Select

For val1 = 67 To 2861 Step 1

Cells(val1, 1).Select

If Cells(val1, 1) = Sheets("tool").Range("G4").Value Then
ActiveCell.Offset(0, 18).Select
Range(ActiveCell, ActiveCell.Offset(3, 0)).Copy

Sheets("Tool").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Else
End If
Next val1

End Sub

Hi, I didn't realize I needed to do that. Thanks for letting me know for the future. Below is the same code in code tags.

Please Login or Register  to view this content.

Thanks John. I've attached a file...it is the same as the first file, except I have added more data. I need to change the code so that when the macro ends, I am looking at the results on the tool spreadsheet. As it is now, it ends and I am looking at cell 73 (which is the last cell in the search range). Also, I notice that even if the code finds what it is looking for early in the loop (say row 23), it still runs until the end ...to row 73...I want the code to tell it to stop after it finds the search criteria.

Thanks again for your help!

Hi John,
I am actually using your approach, but I am trying to learn VBA on my own in the process. I was trying to see if there is a way to fix the issues I mentioned for future use. To scroll to the top, I found that I could use Application.goto, but I'm still working on figuring out how to make that code stop when it finds what it needs.

Thanks for all of your help
Mindy

Hi John,

I have a new issue related to extracting whole ranges of data that I was hoping you could help me with. I still want to find the rows that match the 2 criteria on the "tool" sheet (matching test # and class). This time though, I want to have another button on the "tool" sheet that says "show raw data". When I press it, I want it to:

1. find all of the rows that match the criteria
2. select all of the data associated with those rows (columns A-C)
3. Past the data selection in the worksheet called "specific data". For example, if the criteria selected are Test # 1 and Class: Freshman, I want the VBA to select cells A2:C14 and to paste those.

**When I press the "Show Raw Data" button several times, I assume there may be overlap on the data from the previous paste. Therefore, is there a way to erase the contents of the sheet before a new range is pasted so that only the new raw data is showing?

Many thanks!!
Mindy

Forgot the attachment. Here it is.