Getting Row Number

[AlexanderBB]

I can do this the long way but there may be a clever Excel method
I want the worksheet row number where Col F is "AA" and Col C is not "ABCD"
I'm using VBA but could maybe use Application.WorksheetFunction ?

 

[Peter_SSs]

I can do this the long way

What do you consider the "long way"?

 

[AlexanderBB]

There's gotta be better !

Private Function rowforOtherTitle(LC, Title, Sheet) As Long
    Dim i As Integer
    Dim Dat As Variant
    With Worksheets(Sheet)
        For i = 1 To .Cells(1, "T") 'Number of Rows in Sheet
            If .Cells(i, "F") = LC Then
                Dat = Dat & ";" & i & "@" & .Cells(i, "C")
            End If
        Next
    End With
    Dat = Split(Dat, ";")
    If UBound(Dat) = 2 Then 'Only first two expected / needed
        If Title = Split(Dat(1), "@")(1) Then
                rowforOtherTitle = Val(Dat(2))
            Else
                rowforOtherTitle = Val(Dat(1))
        End If
    End If
End Function

 

[kvsrinivasamurthy]

Try

Sub Macro1()
Dim Cnt&
Cnt = Application.WorksheetFunction.CountIfs(Range("F:F"), "AA", Range("C:C"), "<>ABCD")

End Sub

 

[kvsrinivasamurthy]

IF row numbers are required, M gives array of required row numbers.

Sub rownumbers()
Dim rng1 As Range, rng2 As Range, cel As Range
Dim cri1$, cri2$
Dim M
Set rng1 = Range("F2:F20"): Set rng2 = Range("C2:C20")
cri1 = "AA": cri2 = "ABCD"
M = Filter(Evaluate("transpose(If((" & rng1.Address & "=""" & cri1 & """)*(" & rng2.Address & "<>""" & cri2 & """),Row(" & rng1.Address & "),false))"), False, False)
End Sub

 

[AlexanderBB]

Thanks for reply. If adapted correctly I have

Sub rownumbers(LC, Title, Sheet)
Dim rng1 As Range, rng2 As Range, cel As Range
Dim cri1$, cri2$
Dim M
Dim cnt
With Worksheets(Sheet)

    cnt = Application.WorksheetFunction.CountIfs(.Range("F:F"), LC, .Range("C:C"), "<>" & Title)
    
    Set rng1 = .Range("F1:F" & .Cells(1, "T")): Set rng2 = .Range("C1:C" & .Cells(1, "T"))
    cri1 = LC: cri2 = Title
    M = Filter(Evaluate("transpose(If((" & rng1.Address & "=""" & cri1 & """)*(" & rng2.Address & "<>""" & cri2 & """),Row(" & rng1.Address & "),false))"), False, False)
End With
End Sub

LC is "AA" and Title is "ABCD"
cnt is I and that's right, but M is never anything. I've done something wrong?

 

[kvsrinivasamurthy]

Try
cri1 = "LC": cri2 = "Title"

 

[Fuji]

Returns comma separated row numbers if multiple.
You can add more range, criteria.

Private Function rowforOtherTitle(ParamArray x())
    Dim a, s(1), i&, ii&, n&, temp, w, flg As Boolean
    ReDim a(1 To (UBound(x) + 1) / 2)
    For i = 0 To UBound(x) Step 2
        Select Case True
            Case x(i + 1) Like "<>*": n = 2
            Case x(i + 1) Like "[<>=]*": n = 1
            Case x(i + 1) Like "[<>]=*": n = 3
            Case Else: n = 0
        End Select
        s(0) = IIf(n = 0, "=", Left$(x(i + 1), n))
        s(1) = Mid$(x(i + 1), n + 1)
        If Not IsNumeric(s(1)) Then s(1) = Chr(34) & s(1) & Chr(34)
        a(i / 2 + 1) = Filter(x(i).Parent.Evaluate("transpose(if(" & _
        x(i).Address & s(0) & s(1) & ",row(" & x(i).Address & ")))"), False, 0)
    Next
    For i = 0 To UBound(a(1))
        For ii = 2 To UBound(a)
            temp = Application.Match(a(1)(i), a(ii), 0)
            If IsError(temp) Then flg = True: Exit For
        Next
        If Not flg Then w = w & "," & a(1)(i)
        flg = False
    Next
    If Len(w) Then rowforOtherTitle = Mid(w, 2)
End Function

Book1.xlsm
	A	B	C	D	E	F	G	H	I
1
2			5			AA		2,5,7,8,10
3			2	ABCD
4			5	ABCD		AA		2,5,8
5			4			AA
6			4	ABCD				2,4,5,7,8,9,10
7			2			AA
8			5			AA
9			3	ABCD		AA
10			3			AA
11
Sheet1

Cell Formulas
Range	Formula
H2	H2	=rowforOtherTitle(F2:F10,"AA",D2:D10,"<>ABCD")
H4	H4	=rowforOtherTitle(F2:F10,"AA",C2:C10,">3",D2:D10,"<>ABCD")
H6	H6	=rowforOtherTitle(F1:F10,"AA")