Home:ALL Converter>Appropriate Definitions on a Mismatch Error VBA

Appropriate Definitions on a Mismatch Error VBA

Ask Time:2018-06-08T21:59:26         Author:dom176

Json Formatter

Hello this is an expansion, on a previous question asked Here

Currently I am trying to take a complex excel formula and convert into a application.worksheetfunction VBA process so it's evaluated during code execution.

The biggest issue now is that I am coming across a Mismatch error, post all my definitions. Is it evident to anyone why this would be attracting a mismatch error?

    Dim mws As Worksheet
    Dim wf As WorksheetFunction
    Dim mwsN As Range
    Dim mwsC As Range
    Dim mwsB As Range
    Dim mwsLast As Range

    Set wf = Application.WorksheetFunction
    Set mws = Sheets("Marks")
    Set mwsN = mws.Range("N:N")
    Set mwsC = mws.Range("C:C")
    Set mwsB = mws.Range("B:B")
    LastMWSR = mws.Cells(mws.Rows.Count, "B").End(xlUp).Row
    ClastMWSC = mws.Cells(1, mws.Columns.Count).End(xlToLeft).Column
    Set mwsLast = mws.Cells(LastMWSR + 1, ClastMWSC - 1)


    'error, is thrown just below this line

    mws.Cells(LastMWSR + 1, ClastMWSC).Value = _
    wf.SumIfs(mwsN, mwsB, wf.Index(mwsB, wf.Max(wf.Index((mwsC = mwsLast) * wf.Row(mwsC), 0))), mwsC, mwsLast)

EDIT:

For simplicity please see the excel formula being replicated

=SUMIFS($N:$N,$B:$B,INDEX($B:$B,MAX(INDEX(($C:$C=$AM41)*ROW($C:$C),0))),$C:$C,$AM41)

EDIT2: here is another attempt at the same problem. This way I would insert the formula calculate it then paste as values, though it is still throwing a mismatch error.

mws.Cells(LastMWSR + 1, CLastMWSC).Formula = "=SUMIFS(" & mws.Range("N:N").Address & "," & mws.Range("B:B").Address & ",INDEX(" & mws.Range("B:B").Address & ",MAX(INDEX((" & mws.Range("C:C").Address & "=" & mws.Cells(LastMWSR + 1, CLastMWSC - 1).Address & "*ROW(" & Range("C:C").Address & ",0)))," & mws.Range("C:C") & "," & mws.Cells(LastMWSR + 1, CLastMWSC - 1).Address & ")"

Author:dom176,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/50762326/appropriate-definitions-on-a-mismatch-error-vba
yy