Sumproduct Array in VBA

[hatstand]

I am trying to convert an array formula into vba.

Here is my simplified version:

=IF(SUMPRODUCT((A1:A5="a")*(B1:B5=1))*(C1:C5)=1,True,False)

In this case ‘a’ and ‘1’ are the match I’m looking for, so 'True' would be returned when a match is found. 'False would allow teh code to loop down to the next row. If a match is found, another piece of code I have will paste in a value to the adjacent empty cell.

The data is arranged so there can only ever be one match.

Please say if I’m not being clear enough.

 

[Jerry Sullivan]

This is one way to do this with VBA as a User-Defined Function...

Public Function Found_a11() As Boolean
    Application.Volatile
    Dim i As Long
    With ActiveSheet
        For i = 1 To 5
            If .Cells(i, 1) = "a" And _
                    .Cells(i, 2) = 1 And _
                    .Cells(i, 3) = 1 Then
                Found_a11 = True
                Exit Function
            End If
        Next i
    End With
    Found_a11 = False
End Function

If you were using this in a macro and had a large dataset, you might use a function like autofilters instead of stepping through each row.

 

[Jerry Sullivan]

After re-reading that you want to combine this with some other code to copy the value to an adjacent cell, this would work better.

It will return the Row Number if a match is found or 0 if no match is found.

Public Function Found_a11() As Long
    Dim i As Long
    With ActiveSheet
        For i = 1 To 5
            If .Cells(i, 1) = "a" And _
                    .Cells(i, 2) = 1 And _
                    .Cells(i, 3) = 1 Then
                Found_a11 = i
                Exit Function
            End If
        Next i
    End With
    Found_a11 = 0
End Function