siddwivedi79
New Member
- Joined
- Aug 21, 2015
- Messages
- 1
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
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