VBA Match Statement With Two Conditions?

[Ark68]

I am looking for a VBA solution to find the row number in which two conditions exist. (Match function perhaps ... I've only ever used it with one condition)

Condition one , column H must equal variable pnum
AND
Condition two, column B must equal variable facname

 

[JoeMo]

Hey JoeMo, thanks for your support!

They will! That's what the if statement is trying to determine. So in my testing, criteria 1 (pnum) will return row 2, criteria 2 (fac2) will return row 2, and cireteria 3 (st) will return row 2 (if I have the match of time set up right). With all 3 values matching (row 2) the resulting value cd_rrow should equal 2?

This is my interpretation, but perhaps you're trying to explain sometime I'm not quite picking up on, and for that I apologize. I interpret this single match statement as basically ...

v1 = application.worksheetfunction.match(pnum, ws_cd.Columns(17), 0) 'returns row number of first match of pnum in column 17 eg 2
v2 = application.worksheetfunction.match(fac2, ws_cd.Columns(6), 0)    'returns row number of first match of pnum in column 6 eg 2
v3 = application.worksheetfunction.match(st, ws_cd.Columns(2), 0)        'returns row number of first match of st (variant) in column 2 (h:mmA/P)eg 2
if v1 = v2 = v3 then
     cd_drow = v1
     msgbox "The FIRST row in which all three criteria are satisfied is row : " & cd_drow
Else
     msgbox "There is no row that match all three criteria."
Endif

Thank you for your patience.

Yes, I agree with that. If you step through the snippet of code quoted above, and verify that v1=v2=v3, then if cd_drow is not taking the value of v1, what is its value?

 

[Ark68]

Ha ... this really shouldn't be this complicated should it LOL?
I substituted my code with the snippit above in post 10.
V1, V2 and V3 all equal 2.
But the code

if v1 = v2 = v3 then

fails. It passes over to the "Else" statement where the value for cd_row remains empty.

Is a comparison like V1=V2=V3 not functional?

 

[JoeMo]

That's bad syntax. Try: If V1=V2 and V1=V3 Then ....

 

[Ark68]

Thanks JoeMo. That took care of the comparison problem, but I am stumped on how to find a match for a rounded 'st' to the values as a group in column(2) of the data. Would I round the whole column?

 v3 = Application.WorksheetFunction.Match(Round(st, 3), Round(ws_cd.Columns(2), 3), 0)

Does not produce the results expected with the same data as the previous code to calculate v3.

 

[JoeMo]

I'd create an array of the rounded values in the column to use in the match statement. Here's an example where the col 2 values look like:

Book1.xlsm
	B
1
2	44.32535
3	5.017179
4	98.67647
5	56.60134
6	53.75464
7	47.86623
8	79.78281
9	6.504469
10	98.25196
11	56.85095
12
Sheet4

Sub ArkRound()
Dim V As Variant, x As Variant
V = Columns(2).Value
For i = 1 To UBound(V, 1)
    V(i, 1) = Round(V(i, 1), 3)
Next i
'V is an array that holds the rounded values in column 2
' try matching one of the values in col B, say the value in cell B5
x = Application.Match(Round([B5], 3), V, 0)
If Not IsError(x) Then
    MsgBox x
Else
    MsgBox "no match in column B"
End If
End Sub

 

[Ark68]

Hello JoeMo,

Here is a portion of my routine that I adapted your solution into ..

Sub signatures(srow As Integer, pnum As String, fac2 As String, nrec As Long, st As Variant)
    
    'signatures are assigned based on that facility's assigned crew and eligible shift
    'this assignmnet, whether a signature is required or not, determines the crew assignemnt of this booking
    'srow = the row on the master worksheet being analysed
    
    Dim cd_rrow As Integer 'the row that the current record resides in CORE_DATA
    mbevents = False
    Stop
  
    'find the row (cd_rrow) in CORE_DATA for which the unique currect booking resides (permit#, facility, and start time)
    
    v1 = Application.WorksheetFunction.Match(pnum, ws_cd.Columns(17), 0) 'returns row number of first match of pnum in column 17 eg 2
    v2 = Application.WorksheetFunction.Match(fac2, ws_cd.Columns(6), 0)    'returns row number of first match of pnum in column 6 eg 2
    'v3
        Dim V As Variant, v3 As Variant
        V = ws_cd.Columns(2).Value
        For iv = 1 To UBound(V, 1)
            V(iv, 1) = Round(V(iv, 1), 3)
        Next iv
        'V is an array that holds the rounded values in column 2
        ' try matching one of the values in col B, say the value in cell B5
        v3 = Application.Match(Round(st, 3), V, 0)
        If Not IsError(v3) Then
            MsgBox v3
        Else
            MsgBox "no match in column B"
        End If
    If v1 = v2 And v1 = v3 Then
        cd_drow = v1
        MsgBox "The FIRST row in which all three criteria are satisfied is row : " & cd_drow
    Else
        MsgBox "There is no row that match all three criteria."
    End If

I don't think it's working for me.

When I step through the code, and enter the 'For iv' loop, I am getting a value of 1048576. I can't comment as to whether that is normal or not as I'm unsure what to expect at this point. The loop continues, and on the second pass through iv value increases to 1048577 at which point I am removed out of the routine and taken back to the routine that called 'sub signatures'. It does not continue on through the rest of this routine. It's almost like there is an exit sub in place.

Does this need to be in a routine of it's own to be called? That's about the only difference (other than the variables) that I assumed.

 

[JoeMo]

Hello JoeMo,

Here is a portion of my routine that I adapted your solution into ..

Sub signatures(srow As Integer, pnum As String, fac2 As String, nrec As Long, st As Variant)
   
    'signatures are assigned based on that facility's assigned crew and eligible shift
    'this assignmnet, whether a signature is required or not, determines the crew assignemnt of this booking
    'srow = the row on the master worksheet being analysed
   
    Dim cd_rrow As Integer 'the row that the current record resides in CORE_DATA
    mbevents = False
    Stop
 
    'find the row (cd_rrow) in CORE_DATA for which the unique currect booking resides (permit#, facility, and start time)
   
    v1 = Application.WorksheetFunction.Match(pnum, ws_cd.Columns(17), 0) 'returns row number of first match of pnum in column 17 eg 2
    v2 = Application.WorksheetFunction.Match(fac2, ws_cd.Columns(6), 0)    'returns row number of first match of pnum in column 6 eg 2
    'v3
        Dim V As Variant, v3 As Variant
        V = ws_cd.Columns(2).Value
        For iv = 1 To UBound(V, 1)
            V(iv, 1) = Round(V(iv, 1), 3)
        Next iv
        'V is an array that holds the rounded values in column 2
        ' try matching one of the values in col B, say the value in cell B5
        v3 = Application.Match(Round(st, 3), V, 0)
        If Not IsError(v3) Then
            MsgBox v3
        Else
            MsgBox "no match in column B"
        End If
    If v1 = v2 And v1 = v3 Then
        cd_drow = v1
        MsgBox "The FIRST row in which all three criteria are satisfied is row : " & cd_drow
    Else
        MsgBox "There is no row that match all three criteria."
    End If

I don't think it's working for me.

When I step through the code, and enter the 'For iv' loop, I am getting a value of 1048576. I can't comment as to whether that is normal or not as I'm unsure what to expect at this point. The loop continues, and on the second pass through iv value increases to 1048577 at which point I am removed out of the routine and taken back to the routine that called 'sub signatures'. It does not continue on through the rest of this routine. It's almost like there is an exit sub in place.

Does this need to be in a routine of it's own to be called? That's about the only difference (other than the variables) that I assumed.

1048576 is the number of rows in an Excel sheet. There is no row number 1048577 which is why you are getting an error. The value of Ubound(V,1) should be 1048576 - is it?
Apart from that, it's a bad idea to use an entire column. Why not limit things to the range in column(2) that is actually occupied by relevant data?

 

[Ark68]

OK ... I think this did the trick. I'll know for sure as testing diversifies.

    Dim V As Variant, v3 As Variant
    V = ws_cd.Columns(2).Value
    'For iv = 1 To UBound(V, 1)
    For iv = 2 To nrec
        V(iv, 1) = Round(V(iv, 1), 3)
    Next iv
    v3 = Application.Match(Round(st, 3), V, 0)
    If Not IsError(v3) Then
        MsgBox v3
    Else
        MsgBox "no match in column B"
    End If

Thank you sooo much JoMoe. You're support is greatly appreciated, and I enjoyed working through your suggestions without you simply giving me the answer! It was a learning opportunity as much as a solution.

 

[JoeMo]

OK ... I think this did the trick. I'll know for sure as testing diversifies.

    Dim V As Variant, v3 As Variant
    V = ws_cd.Columns(2).Value
    'For iv = 1 To UBound(V, 1)
    For iv = 2 To nrec
        V(iv, 1) = Round(V(iv, 1), 3)
    Next iv
    v3 = Application.Match(Round(st, 3), V, 0)
    If Not IsError(v3) Then
        MsgBox v3
    Else
        MsgBox "no match in column B"
    End If

Thank you sooo much JoMoe. You're support is greatly appreciated, and I enjoyed working through your suggestions without you simply giving me the answer! It was a learning opportunity as much as a solution.

You are welcome- thanks for the reply. One thing I wanted to communicate with the example code of post #15 is that Application.Match does not cause a runtime error if no match exists, so you can test for no match by using IsError(v3). That allows you to decide what you want to do in that instance and implement your decision in the code. On the other hand, WorksheetFunction.Match causes a runtime error if no match is found.