PDA

View Full Version : VBA needed to extract data from specific cells in different worksheet



woolen
08-01-2011, 09:31 AM
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
08-01-2011, 03:06 PM
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
08-02-2011, 07:30 AM
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
08-02-2011, 09:45 AM
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
08-03-2011, 03:01 PM
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 :)