VBA Index/Match with multiple criteria (unique value & date)

Question

I have a spreadsheet that has values for more than one month, so I am trying to first find the value based on a value in the wsRevFile worksheet and then ensure that this is the value from last month. When I use the following code, I get a "invalid number of arguments" error.

Sub RevLookup(wsMvFile As Worksheet, wsRevOld As Worksheet, wsNewRev As Worksheet, _
                        rowCount As Integer, workCol As String, _
                        srcCol1 As Integer, srcCol2 As Integer)

Dim vrw As Variant, i As Long

For i = 2 To rowCount
    vrw = Application.Match(wsRevFile.Range("A" & i), wsNewRev.Columns(2), Format(DateSerial(Year(Date), Month(Date), 0), "mm/dd/yyyy"), wsNewRev.Columns(1), 0)
    If IsError(vrw) Then
        vrw = Application.Match(wsRevFile.Range("A" & i), wsRevOld.Columns(1), 0)
        If Not IsError(vrw) Then _
            wsRevFile.Range(workCol & i) = Application.Index(wsRevOld.Columns(srcCol1), vrw)
    Else
        wsRevFile.Range(workCol & i) = Application.Index(wsNewRev.Columns(srcCol2), vrw, 1)
    End If
Next i
End Sub

I am assuming this has to do with the way I assigned the Application Match function, because the formula without this part works for other columns. Any ideas on how I could get this to work?

Thanks for your help!

Answer 1

Try ajusting the variables of the following procedure, as I didn't figure out your input and output data:

Sub Main()
Dim SearchValue As Variant
Dim SearchColumn As Range
Dim ReturnColumn As Range
Dim ResultRows As Collection
Dim LastDate As Variant 'Date?
Dim iRow As Variant

SearchValue = 10 '<-- change to suit
Set SearchColumn = wsNewRev.Range("B1:B10")
Set ReturnColumn = wsNewRev.Range("C1:C10") '<-- change to suit

Set ResultRows = GetLoopRows(SearchColumn, SearchValue)
For Each iRow In ResultRows
    If LastDate < ReturnColumn(iRow) Then
        LastDate = ReturnColumn(iRow)
    End If
Next iRow

Debug.Print LastDate
End Sub

Function GetLoopRows(ParamArray pParameters() As Variant) As Collection
'Obtém limites de laços com levando em conta condições
'[vetor1], [valor1], [vetor2], [valor2], ...

Dim iCondition As Long
Dim i As Variant
Dim iRow As Variant
Dim Result As Collection
Dim NumConditions As Long
Dim SearchCollection As Collection
Dim ArraysCollection As Collection
Dim iArray As Variant

NumConditions = (UBound(pParameters) - LBound(pParameters) + 1) / 2
Set ArraysCollection = New Collection
Set SearchCollection = New Collection
For i = LBound(pParameters) To UBound(pParameters) Step 2
    ArraysCollection.Add pParameters(i + 0).Value2
    SearchCollection.Add pParameters(i + 1)
Next i

Set Result = New Collection
For iRow = LBound(ArraysCollection(1)) To UBound(ArraysCollection(1))
    For iCondition = 1 To NumConditions
        If ArraysCollection(iCondition)(iRow, 1) <> SearchCollection(iCondition) Then GoTo Continue
    Next iCondition
    Result.Add CLng(iRow)
Continue:
Next iRow
Quit:
Set GetLoopRows = Result
End Function