FINDING A ROW THAT MEETS TWO CONDITIONS

Posted by MAURO on June 27, 2000 10:27 AM

can someone please help me.

I have a table with 3 colums:
County,school number,name of school.

How do I find the school name by imputing
county and school number "two conditions"

I've tried using match but it only works on one condition. The problem is that several schools may
share the same number but not within the same county.

Thanks,

Mauro
Miami, FL.

Posted by Ryan on June 27, 0100 12:57 PM

Hey Mauro,
'This assumes that the county information is in Column A, The School Number is in
'Column B, and the School name is in Column C. It also assumses that there are
'no empty rows between school. If this is not the case you can
'change it, or if you don't know how, let me know how it is set up so I can make
'the appropriate changes. Hope you like it. Let me know
'Ryan

Sub FindSchool()
Dim County As String
Dim SchoolNum As Variant
Dim CellRow As Integer

Application.ScreenUpdating = False
County = InputBox("Enter County", "County")
If County = "" Then Exit Sub
SchoolNum = InputBox("Enter School Number", "School Number")
If SchoolNum = "" Then Exit Sub

x = 1
FindNext:

Do While Cells(x, 1).Value &LT;&GT; County
If Cells(x, 1).Value = "" Then
MsgBox "Unable to find", vbCritical, "Error"
Exit Sub
End If
x = x + 1
CellRow = x
Loop

If Range("A" & CellRow).Offset(0, 1).Text &LT;&GT; SchoolNum Then
x = x + 1
GoTo FindNext
Else
MsgBox Range("C" & CellRow).Value
End If
Application.ScreenUpdating = True
End Sub

Posted by Mauro on June 27, 0100 2:11 PM

Thanks Ryan,

This macro does work, but I need a function that I can enter in a separate sheet that will refer to the sheet that has the County,number and school name, and provide me with the school name.

I should be able to type in a cell county and school number and get schoolname in the next cell

Thanks

county school no. name
1 10 a
1 11 b
1 12 c
1 21 d
1 15 e
2 31 f
2 08 g
2 10 h
2 88 i
2 22 j

Posted by Mauro on June 28, 0100 3:23 PM

Thank you Ryan.

If Sheets("Sheet1").Range("A" & x).Value = "" Then MsgBox "Unable to find", vbCritical, "Error" Exit Sub If Sheets("Sheet1").Range("A" & CellRow).Offset(0, 1).Value &LT;&GT; SchoolNum Then

Posted by Ryan on June 27, 0100 4:12 PM

Hi,
Here is the updated code. I tried to do it as a UDF, but you can't manipulate the way I needed to when using a function. To use this macro, activate the cell you want to put the school name into(next to the county, and school number info) and run the macro. You can make it easy and program a shortcut key for this macro, just go to Tools --> Macro --> Macros and highlight this macro and click on the options button. Hope this gets done what you want it to get done. Let me know. I always like to hear how things are going.
Ryan

Sub FindSchool()
Dim County As String
Dim SchoolNum As Variant
Dim CellRow As Integer
Dim x As Integer

Application.ScreenUpdating = False
County = ActiveCell.Offset(0, -2).Value
If County = "" Then Exit Sub
SchoolNum = ActiveCell.Offset(0, -1).Value
If SchoolNum = "" Then Exit Sub

x = 1
CellRow = 1
FindNext:

Do While Sheets("Sheet1").Range("A" & x).Value &LT;&GT; County

If Sheets("Sheet1").Range("A" & x).Value = "" Then
MsgBox "Unable to find", vbCritical, "Error"
Exit Sub
End If
x = x + 1
CellRow = x
Loop

If Sheets("Sheet1").Range("A" & CellRow).Offset(0, 1).Value &LT;&GT; SchoolNum Then
x = x + 1
CellRow = x
GoTo FindNext
Else
ActiveCell.Value = Sheets("Sheet1").Range("C" & CellRow).Value
End If
Application.ScreenUpdating = True
End Sub