Excel VBA - MAX IF with multiple conditions | Please help

[siddwivedi79]

Hello folks

I am novoice in VBA programming and am stuck on a program, need help from you experts.
I did read many threads on this subject but issue remains..

I am looking to use Max if function (multiple conditions) in VBA using Evaluate but everytime I run this program I get the "Type mismatch error"

Program
I have 2 sheets in the workbook "Data" and "Records"
The data sheet has all the data (about 50000 records) and the records sheet will get me the data I want to look at
I am trying to use max if function in "records" sheet which looks at the records on "data" sheet and gives the max value based on the criteria
Below is the code I am using, please help me in identifying the error
---------------------------------------------------------------------------------------

Option Explicit
Dim i,As Integer
Dim countOnRoleSheet, countOnDataSheet As Variant
Dim rngRoleReq, rngDataActivity, rngDataReq, rngDataDate As Range
Dim maxAns As Variant

----------------------------------------------------------------------------------------


Worksheets("roles").Select
countOnRecordSheet = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Worksheets("data").Select
countOnDataSheet = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

Set rngDataReq = Worksheets("data").Range("A:A")
Set rngDataDate = Worksheets("data").Range("E:E")
Set rngDataActivity = Worksheets("data").Range("D:D")


For i = 2 To countOnRecordSheet
Set rngRoleReq = Worksheets("records").Range("A" & i)
maxAns = Evaluate("=MAX(IF(" & rngDataReq & "=" & rngRoleReq.Value & ",if(" & rngDataActivity & "='Completed'," & rngDataDate & ")))")
MsgBox maxAns
Next

---------------------------------------------------------------------------------------------


So once I run this loop for each record in the "records" sheet it should look for records in "data sheet" which match Rolereq (Recrods-Col A) with Datareq(Data - Col A) and Activity (Data - Col D) that have "completed" as value, and then find the max of date (Data-col E)

I hope I am able to illustrate the problem.

Looking forward to you help here.


Thanks in advance

S