Find row number by two criteria

[YasserKhalil]

Hello everyone
I have data in column C and column F
I would like to match two criteria : the first is 172040808 from column C and the second is 8111006 from column F
I would prefer VBA as this is part of another code that is required to be able to complete it
Attachment is to illustrate the issue (Note : I don't need loops if possible as the number of real rows are too large)

[HansV]

There is a problem with the values in column C.
For example, the formula =C26=I7 returns False, while =C26-I7 returns 0. Have the data in column C been pasted from another application?
You can correct it by selecting column C and then executing

Selection.Value = Selection.Value

Your formula would then return 26. You can use the formula in VBA by applying Evaluate.

[YasserKhalil]

Thanks a lot Mr. Hans
Can you help me using Evaluate to get the row number?

[HansV]

Apply Evaluate to your formula.

[YasserKhalil]

Please correct me if there are any errors

Code: Select all

Sub MyTest()
    Dim r1 As Range
    Dim r2 As Range
    Dim c1 As String
    Dim c2 As String
    
    With Sheet1
        Set r1 = .Range("C1:C" & .Cells(Rows.Count, 3).End(xlUp).row)
        Set r2 = .Range("F1:F" & .Cells(Rows.Count, 6).End(xlUp).row)
    End With
    c1 = "172040808": c2 = "8111006"
    
    MsgBox Evaluate("MATCH(1,INDEX((" & r1.Address & "=" & c1 & ")*(" & r2.Address & "=" & c2 & "),),0)")
End Sub

Can UDF be created for that purpose ..?
I imagine FindRow(r1 as Range, c1 as String, r2 as Range, c2 as String)

[HansV]

You almost have the UDF. It's easy to convert your Sub to a Function. Try it!

[YasserKhalil]

I got it

Code: Select all

Function FindRow(r1 As Range, c1 As String, r2 As Range, c2 As String)
    FindRow = Evaluate("MATCH(1,INDEX((" & r1.Address & "=" & c1 & ")*(" & r2.Address & "=" & c2 & "),),0)")
End Function

and use it

Code: Select all

MsgBox FindRow(r1, c1, r2, c2)

[HansV]

Yep, that's it!

[YasserKhalil]

Thank you very much Mr. Hans
And I welcome any more solutions if found

Q: Can this solution be suitable for strings?

[HansV]

Code: Select all

Function FindRow(r1 As Range, c1 As String, r2 As Range, c2 As String)
    FindRow = Evaluate("MATCH(1,INDEX((" & r1.Address & "=""" & c1 & """)*(" & r2.Address & "=""" & c2 &"""),),0)")
End Function

[YasserKhalil]

Thanks a lot Mr. Hans
It works with strings and the first one works only for numbers
What I mean : I have mixed data sometimes so is it possible to make it flexible to deal with both types of data ?

[HansV]

Test this version:

Code: Select all

Function FindRow(r1 As Range, c1 As Variant, r2 As Range, c2 As Variant)
    Dim s1 As String
    Dim s2 As String
    If Not IsNumeric(c1) Then
        s1 = Chr(34)
    End If
    If Not IsNumeric(c2) Then
        s2 = Chr(34)
    End If
    FindRow = Evaluate("MATCH(1,INDEX((" & r1.Address & "=" & s1 & _
        c1 & s1 & ")*(" & r2.Address & "=" & s2 & c2 & s2 & "),),0)")
End Function

[YasserKhalil]

Thanks a lot Mr. Hans
Now it is wonderful and perfect. Thank you very much.

[Doc.AElstein]

Yasser,..
Just a small observation….
In the spreadsheet, you need the “trick” of INDEX( _____ , )
In the spreadsheet , INDEX( _____ , ) will make ($C$1:$C$30=$I$7)*($F$1:$F$30=$J$7) an array

The trick will make it work similar to an array ( CSE type 2 ) entry

If you change your formula to =MATCH(1,($C$1:$C$30=$I$7)*($F$1:$F$30=$J$7),0) and then CSE entry, then it will also work – see range I9 in your returned file

Usually results are there, but sometimes we need tricks or CSE entry to see them

Evaluate in VBA will usually recognise multi cell ranges during evaluation and return an array. Usually if a formula works with CSE entry , then it will work in Evaluate.

For your function you are using Evaluate.
You can simplify your function to
FindRow = Evaluate("MATCH(1,(" & r1.Address & "=" & c1 & ")*(" & r2.Address & "=" & c2 & "),0)")

So for, example, Hans latest solution like this will also work:

Code: Select all

 Function FindRow(r1 As Range, c1 As Variant, r2 As Range, c2 As Variant)
Dim s1 As String, s2 As String
    If Not IsNumeric(c1) Then
     Let s1 = Chr(34)
    End If
    If Not IsNumeric(c2) Then
     Let s2 = Chr(34)
    End If
 Let FindRow = Evaluate("MATCH(1,(" & r1.Address & "=" & s1 & c1 & s1 & ")*(" & r2.Address & "=" & s2 & c2 & s2 & "),0)")
End Function

_._______________________________________________________________________
Here is another variation on an earlier solution from Hans. This might also work in some situations when other things don’t.
I am not 100% sure what I am doing here, but I think the general idea is that this will force string comparisons in all cases.
I think this solution will work for any combinations of strings or numbers anywhere…. (Note it modifies slightly your original ranges, and would also automatically overcome problems like your original one in the first post )

Code: Select all

 Function FindRow(r1 As Range, c1 As String, r2 As Range, c2 As String)
 Let r1.Value = Evaluate("=" & """=""" & "&" & """""""""" & "&" & r1.Address & "&" & """""""""")
 Let r2.Value = Evaluate("=" & """=""" & "&" & """""""""" & "&" & r2.Address & "&" & """""""""")
 Let FindRow = Evaluate("MATCH(1,(" & r1.Address & "=""" & c1 & """)*(" & r2.Address & "=""" & c2 & """),0)")
End Function

Alan

Ref:
http://www.eileenslounge.com/viewtopic. ... 98#p196259" onclick="window.open(this.href);return false;

[YasserKhalil]

That's great too Mr. Alan
Thank you very much for your solutions