VBA find row for double conditions

[Bundi999]

Dear all,

I would once again really appreciate your help on the following below:

I need to find the row number when two conditions are met:

1. First, find in column 23 the value x
2. Second, find in column 91 the value shop

Wheb I run the code, it doesnt work yet. Do you have any tips?

Thanks!!

If wscalc.Columns(23).Find(x, , , xlWhole)), , , xlWhole) And wscalc.Columns(91) = shop Then

r = activecell.row

[dflak]

I am not quite sure of the requirement, but this code will find where "x" is found in column 23, and then starting at that row, look for "shop" in column 91.

Dim wscalc As Workbook
Dim rng As Range

Set rng = wscalc.Columns(23).Find("x", LookIn:=xlValues, lookat:=xlWhole)

Set rng = wscalc.Columns(91).Find("shop", after:=wscalc.Cells(rng.Row - 1, 91), LookIn:=xlValues, lookat:=xlWhole)

[yujin]

I guess Bundi wants the row where column 23 has "x" and column 91 has "shop".
Then, try the code below.

Sub Macro1()
    Dim found As Range, firstFound As Range
    Dim col1 As Long, col2 As Long
    Dim str1 As String, str2 As String
    Dim r As Long
    
    col1 = 23: str1 = "x"
    col2 = 91: str2 = "shop"
    
    With wscalc.Columns(col1)
        Set found = .Find(What:=str1, LookAt:=xlWhole)
        If Not found Is Nothing Then
            If Cells(found.Row, col2) = str2 Then
                r = found.Row
            Else
                Set firstFound = found
                Do
                    Set found = .FindNext(found)
                    If Not found Is Nothing Then
                        If Cells(found.Row, col2) = str2 Then
                            r = found.Row
                            Exit Do
                        End If
                    End If
                Loop Until found.Address = firstFound.Address
            End If
        End If
    End With

    MsgBox r
End Sub

[jindon]

Try

Sub test()
    Dim x, shop, a, b
    x = "x"
    shop = "shop"
    If Not IsNumeric(x) Then x = Chr(34) & x & Chr(34)
    If Not IsNumeric(shop) Then shop = Chr(34) & shop & Chr(34)
    With ActiveSheet
        a = Intersect(.UsedRange, .Columns(23)).Address
        b = Intersect(.UsedRange, .Columns(91)).Address
        a = Filter(.Evaluate("transpose(if((" & a & "=" & x & ")*(" & b & "=" & shop & "),row(" & a & ")))"), False, 0)
        If UBound(a) > -1 Then
            MsgBox "Found in" & vbLf & Join(a, vbLf)
        Else
            MsgBox "Not found"
        End If
    End With
End Sub

[Bundi999]

Dear Jindon,

Indeed, your code works superfast with the same principle, very smart indeed.

I just have one stupid question; iso returning a message box with the line row, can a just be assigned with the row found and stop therafter?

Thanks!!!!!

[Bundi999]

yujin, your code works great! THank you all for your help!

[yujin]

You are welcome, Bundi. Thank you for your feedback.
Personally, jindon's code, using an array, interests me.

[Bundi999]

Your help would be highly appreciated!

[jindon]

You mean like this?

Sub test()
    Dim x, shop, a, b, myRow As Long
    x = "x"
    shop = "shop"
    If Not IsNumeric(x) Then x = Chr(34) & x & Chr(34)
    If Not IsNumeric(shop) Then shop = Chr(34) & shop & Chr(34)
    With ActiveSheet
        a = Intersect(.UsedRange, .Columns(23)).Address
        b = Intersect(.UsedRange, .Columns(91)).Address
        myRow = .Evaluate("min(if((" & a & "=" & x & ")*(" & b & "=" & shop & "),row(" & a & ")))")
        If myRow > 0 Then
            Application.Goto .Cells(myRow, 23), True
        Else
            MsgBox "Not found"
        End If
    End With
End Sub