Need VBA code for selecting row that meets 2 criteria

[woolen]

Hello. I am new to VBA and could use some help, please!!

I am working with 2 worksheets, one called "tool" and the other called "data". In the "tool" worksheet, I have cells A1 and B1 where the user can enter a value (A1 has to be an integer and B1 is a word).

In the "data" spreadsheet, I have data in columns A and B from rows 2 to 24. I need a code that will find and select the first cell in the row where A1 in the "tool" worksheet matches a cell in column A of the "data" sheet AND where B1 of the "tool" worksheet matches in column B of the "data" sheet. I want to select the first cell in the first row where BOTH criteria are met.

I have played around with Find and Select case commands, but haven't been able to get it to work. Please help!!

[Trebor76]

Hi woolen,

As your dataset is quite small, the following will suffice:


Option Explicit
Sub Macro2()

Dim strSearchKey As String
Dim lngLastRow As Long
Dim blnMatchFound As Boolean
Dim rngCell As Range

'Ensure there's a numeric entry in cell A1 of the 'tool' tab.
If Len(Sheets("tool").Range("A1")) = 0 Then
MsgBox "There is no integer value entered in cell A1 of the ""tool"" tab.", vbInformation, "My Search Application"
Exit Sub
ElseIf IsNumeric(Sheets("tool").Range("A1")) = False Then
MsgBox """" & Sheets("tool").Range("A1") & """ is not an integer. Please re-enter and try again.", vbInformation, "My Search Application"
Exit Sub
End If

'Ensure there's a text entry in cell B1 of the 'tool' tab.
If Len(Sheets("tool").Range("B1")) = 0 Then
MsgBox "There is no text entered in cell B1 of the ""tool"" tab.", vbInformation, "My Search Application"
Exit Sub
ElseIf IsNumeric(Sheets("tool").Range("B1")) = True Then
MsgBox """" & Sheets("tool").Range("B1") & """ is not a text entry. Please re-enter and try again.", vbInformation, "My Search Application"
Exit Sub
End If

strSearchKey = Sheets("tool").Range("A1") & Sheets("tool").Range("B1")
lngLastRow = Sheets("data").Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
blnMatchFound = False

Application.ScreenUpdating = False

For Each rngCell In Sheets("data").Range("A2:A" & lngLastRow)

If rngCell.Value & rngCell.Offset(0, 1).Value = strSearchKey Then
With Sheets("data")
.Select 'Need to be on the tab to select cells.
.Range("A" & rngCell.Row & ":B" & rngCell.Row).Select
End With
blnMatchFound = True
Exit For
End If

Next rngCell

Application.ScreenUpdating = True

If blnMatchFound = False Then
MsgBox "There no matches found for """ & Sheets("tool").Range("A1") & " " & Sheets("tool").Range("B1") & """ on the ""data"" tab.", vbInformation, "My Search Application"
End If

End Sub

I dare say it would be far more efficent to just have filters on Row 2 the 'data' tab that you can simply check (filter) the data on.

HTH

Robert