VBA needed to extract data from specific cells in different worksheet

[woolen]

Hello. I am 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

[maggie]

Here is a search and copy code I created searching for keywords. You can modify this code for a second search criteria and customize how and where you want things pasted. Good Luck

~ Maggie

Program Below

Private Sub cmdSearch_Click()
Worksheets("Home").Select
Range("A3:G65536").Select
Selection.Clear
Selection.RowHeight = StandardHeight
Dim ws As Worksheet, myvar As String, val1 As Range
Dim val2 As Range, tmp As Range, cnt As Integer
cnt = 0
myvar = InputBox("Please Enter a Keyword:")
If myvar = "" Then Exit Sub
    For Each ws In ThisWorkbook.Worksheets
        Set val1 = ws.Cells.Find(What:=myvar, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
        If Not val1 Is Nothing Then
            cnt = cnt + 1
            Application.Goto val1
            ActiveCell.EntireRow.Select
            Selection.Copy
            Worksheets("Home").Select
            Range("A1").Select
            Selection.End(xlDown).Offset(1, 0).Select
            ActiveSheet.Paste
            Set tmp = val1
            again:
            Set val2 = ws.Cells.FindNext(After:=val1)
            If val1.Address <> val2.Address And tmp.Address <> val2.Address Then
                Application.Goto val2
                ActiveCell.EntireRow.Select
                Selection.Copy
                Worksheets("Home").Select
                Range("A2").Select
                Selection.End(xlDown).Offset(1, 0).Select
                ActiveSheet.Paste
                Set val1 = val2
                GoTo again
            End If
        End If
    Next ws
If cnt = 0 Then MsgBox "No Matches Found"
End Sub

[woolen]

THanks Maggie! I will be working on making this work today. Instead of putting in a keyword, I will need to reference a cell (always the same cell). Can I just put the cell reference in where the code says "Please enter a Keyword", or will the code need to change in some other way?

[maggie]

If the reference cell is not changing you can absolutely put it there.

myvar= ws.Cell("B3")

or something like that. The help menu is actually really helpful for syntax issues.

As the code basically runs on myvar being different each time you could get rid of the useless lines. (If myvar = "" etc) you already know what myvar would = therefore could yank that out of the code.

gl

[woolen]

I'm having trouble adding a second criteria to the code...any ideas on the best way to code: if cell C3 matches a cell in Column A AND if C5 matches a cell in column B, then select that cell? I can't figure out what to put in the code to let it know that I'm looking for the first row where BOTH criteria are met.

Thanks