VBA search 2 column values, return Row #

[Rlumpy]

This is to enter a Module Name in letters (column A) and the pipe number (column b) with 4 radius distances then place that percentage into column AJ (36 columns to the right of the RowMatch value.
in plain excel I can use: =SMALL(IF((A:A=ModuleName)*(B:B=Pipe#),ROW(A:A),""),1)
and that seemed pretty simple but I have no idea how to convert that to VBA language. I recorded a macro to see what it does but that did not help me see the coding, it was specific for every exact cell I clicked, not the 2 columns I need to search.

I would love some assistance on my rowMatch line. It was working with rowMatch = WorksheetFunction.Match(Pipe, Range("A:A"), 0)
but now I need to match with Module Name AND the Pipe Number columns to find the exact row.

Thank you kindly in advance for any help.
-------------------
Private Sub cb_OK_Click()

Dim Module As String
Dim Pipe As Integer, Top As Double, Bot As Double, Lf As Double, Rt As Double, dX As Double, dY As Double, OvMath As Double
Dim rowMatch As Long

Module = Me.t_Module.Value
Pipe = Me.t_Pipe.Value
Top = Me.t_Top.Value
Bot = Me.t_Bot.Value
Lf = Me.t_Lf.Value
Rt = Me.t_Rt.Value
dX = Lf + Rt
dY = Top + Bot

OvMath = 2 * ((WorksheetFunction.Max(dX, dY) - WorksheetFunction.Min(dX, dY)) / (WorksheetFunction.Max(dX, dY) + WorksheetFunction.Min(dX, dY)))

HELP HERE: rowMatch = WorksheetFunction.Small(If(((Range("A:A")= Module)*(Range("B:B")= Pipe),Row(Range("A:A")),""""),1))
Cells(rowMatch, 36).Value = OvMath

MsgBox ("X dia = " & dX & vbCrLf & "Y dia = " & dY & vbCrLf & "Ovality = " & Format(OvMath, "#.00000") * 100 & "% placed in Pipe # " & Pipe & " for Mod " & Module)

Unload Me

End Sub

 

[Alex Blakenburg]

Try this:

    Module = """" & Me.t_Module.Value & """"
    Pipe = """" & Me.t_Pipe.Value & """"

    rowmatch = Evaluate("=SMALL(IF((A:A=" & Module & ")*(B:B=" & Pipe & "),ROW(A:A),""""),1)")

PS: If everyone using the macro is using 365 you could also use XLookup

 

[Rlumpy]

Thank you so much, Alex.
That fixed my issue. I think I know a lot and then I learn something new, I've never used Evaluate before.
Very much appreciated.

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.