Find row where two cells equal some criteria (1 Viewer)

[Margarita]

Hello,
This is I guess a purely vba in Excel question, but I'm trying my luck here anyway. I've been trying to understand how to use the find method in vba to locate a row where Column A equals something and column B equals something else. I know that in my sheet this row will be unique and I'm trying to get this row number. I was only successful with getting it to work with locating the value in column A:

foundcell = Sheets("ZIP codes").Range("A1:A50000").Find(what:=ZipCode, _
LookIn:=xlValues, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
 
ZipRow = foundcell.Row

Would someone be able to show me how to exapnd this to search for that unique combination of col A and col B?
Thank you!!

 

[Brianwarnock]

I've never done this as I would write code reading cells in the column but I think what you have to do is use findnext and also on each hit check the other column

http://msdn.microsoft.com/en-us/library/office/ff196143.aspx

Shows the use of findnext

Brian

 

[Margarita]

I've never done this as I would write code reading cells in the column but I think what you have to do is use findnext and also on each hit check the other column

http://msdn.microsoft.com/en-us/library/office/ff196143.aspx

Shows the use of findnext

Brian

Hello, thanks for the link. The find method with multiple columns seems to be more trouble than it's worth. What I'm really seeking is autofilter by these two columns and then get the number of the only visible row that's left after filter is applied. I remember having a lot of trouble with getting autofilter to work through vba on several different occasions, so I developed a fear of it and therefore I now try to stick to the find method wherever I can. I will post the solution when I arrive at it.
Thanks!

 

[Brianwarnock]

Why are you trying to use autofilter in VBA.

Do you want to see the row?
or
Do you want to use the row number in VBA?

Brian

 

[Margarita]

Why are you trying to use autofilter in VBA.

Do you want to see the row?
or
Do you want to use the row number in VBA?

Brian

I am trying to just use the row number where the conditions in column A and B are met. I know this row is unique and therefore was trying to use Find but the method confuses me. All I need really is that one row number.

 

[Brianwarnock]

Spent a lot of time failing to get Match worksheet function to work with concatenated cols in VBA, works ok on the worksheet but not in VBA

so went to plan B and wrote a bit of rough code to use Find and FindNext
this works on a little data I had.
My search columns are D and F in sheet3.
the searched for are in A1 and B1 in Sheet1, I don't know how you intend to input your criteria.

It is to illustrate how to do it, not classy tidied up code
hope it helps

Brian

Sub a()
Dim myrow As Long
Dim firstcheck As String
Dim seccheck As String

firstcheck = Worksheets("sheet1").Range("A1")
seccheck = Worksheets("Sheet1").Range("B1")

With Worksheets("Sheet3").Range("d1:d7")
    Set c = .Find(firstcheck, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        myrow = c.Row
        If c.Offset(0, 2) = seccheck Then GoTo endst ' column F has second value
        Do
            Set c = .FindNext(c)
          If c.Offset(0, 2) = seccheck Then
          myrow = c.Row
          GoTo endst
          End If
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With
endst:
Debug.Print myrow
End Sub

 

[Margarita]

Spent a lot of time failing to get Match worksheet function to work with concatenated cols in VBA, works ok on the worksheet but not in VBA

so went to plan B and wrote a bit of rough code to use Find and FindNext
this works on a little data I had.
My search columns are D and F in sheet3.
the searched for are in A1 and B1 in Sheet1, I don't know how you intend to input your criteria.

It is to illustrate how to do it, not classy tidied up code
hope it helps

Brian

Sub a()
Dim myrow As Long
Dim firstcheck As String
Dim seccheck As String
 
firstcheck = Worksheets("sheet1").Range("A1")
seccheck = Worksheets("Sheet1").Range("B1")
 
With Worksheets("Sheet3").Range("d1:d7")
    Set c = .Find(firstcheck, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        myrow = c.Row
        If c.Offset(0, 2) = seccheck Then GoTo endst ' column F has second value
        Do
            Set c = .FindNext(c)
          If c.Offset(0, 2) = seccheck Then
          myrow = c.Row
          GoTo endst
          End If
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With
endst:
Debug.Print myrow
End Sub

Hello, thank you so much for giving this a shot. I was going back and forth between the find and filter idea and not succeeding at either. I am almost out the door to go home- I will tailor this to my needs and test it first thing tomorrow morning and post back. Thank you very, very much for taking time to help me. I love this forum and members like you!

 

[Brianwarnock]

Thanks to NBVC who pointed me at the Evaluate function, in the Excel forum, I have now got the Match to work, this simplifies the code to

Dim myrow As Long
myrow = Evaluate("MATCH(""jo annsmythe"",(Sheet3!D$1:D$8)&(Sheet3!F$1:F$8),0)")

This shows the use with hardcoded criteria, for clarity you may prefer

Dim myrow As Long
myrow = Evaluate("MATCH(""jo ann"" & ""smythe"",(Sheet3!D$1:D$8)&(Sheet3!F$1:F$8),0)")

I prefer to use a search form containing the criteria and the command button that runs the code, in which case

Dim myrow As Long
myrow = Evaluate("MATCH(A1&B1,Sheet3!D$1:D$8)&(Sheet3!F$1:F$8),0)")

Hope this helps

Brian

 

[Margarita]

Thanks to NBVC who pointed me at the Evaluate function, in the Excel forum, I have now got the Match to work, this simplifies the code to

Dim myrow As Long
myrow = Evaluate("MATCH(""jo annsmythe"",(Sheet3!D$1:D$8)&(Sheet3!F$1:F$8),0)")

This shows the use with hardcoded criteria, for clarity you may prefer

Dim myrow As Long
myrow = Evaluate("MATCH(""jo ann"" & ""smythe"",(Sheet3!D$1:D$8)&(Sheet3!F$1:F$8),0)")

I prefer to use a search form containing the criteria and the command button that runs the code, in which case

Dim myrow As Long
myrow = Evaluate("MATCH(A1&B1,Sheet3!D$1:D$8)&(Sheet3!F$1:F$8),0)")

Hope this helps

Brian

Hello, I forgot to reply to your post- thank you VERY much for referencing the Evaluate function. I have never heard of it before and looked up the documentation for it after reading your post. It looks like a very powerful function. Thanks again.
As for my problem- I ended up using just autofilter:

Application.ScreenUpdating = true
 
With Sheets("WorkValues")
.AutoFilterMode = False
If Not Sheets("Calcs").Range("K" & i) = vbNullString Then
Modifier = Sheets("Calcs").Range("K" & i).Value
            .Range("A1:Z1").AutoFilter
            .Range("A1:Z1").AutoFilter field:=1, Criteria1:=CPTCode
            .Range("A1:Z1").AutoFilter field:=2, Criteria1:=Modifier
Else
            .Range("A1:Z1").AutoFilter
            .Range("A1:Z1").AutoFilter field:=1, Criteria1:=CPTCode
            .Range("A1:Z1").AutoFilter field:=2, Criteria1:="="
End If
End With
 
Set foundcell = .Cells.Find(what:=CPTCode, _
After:=.Cells(1, 1), _
LookIn:=xlValues, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

Thanks.