PDA

View Full Version : Solved: Excel function: Sumproduct to match certain veriables with a date parameter



Aramazd
04-19-2012, 09:05 AM
Hello,

This is my second attempt seeking help, im quite new to programming however I did manage to write a neat program on excel for my workmates which deals with reports.

Currently I need a VBA format formula that calculates a CountIf statement with 2 different columns.
#Note this is pulled from a different sheet in the same workbook.
Column 1 = Name
Column 3 = Date, DateFormat = "24/10/2011"

Here's the following code that I tried to compile.

Try 1
Dim Name As String
Dim Date1 As Double
Dim iRequestTest As String
Name = Worksheets("UK-Oracle-Financials").Range("D29").Value
Date1 = Worksheets("Requests").Range("H12781").Value
iRequestTest =Application.SumProduct((Sheets("Requests").Range("H1:H65535") _
= " & Date1 & ") * (Sheets(Requests).Range("C1:C65535") = """ & Name _& """))
Application.Evaluate (iRequestTest )
Worksheets("UK-Oracle-Financials").Range("L23").Value = iRequestTes

Try 2
iRequestTest = Evaluate("=SUMPRODUCT(--(Requests!H1:H65535<=dateMarch2012End), _
(Requests!H1:H65535>=date*(Requests!D1:D65535="Marcus Comins"))))
Try 3
RequestsTest = iRequestsTest + Application.WroksheetFunction.SumProduct(--Sheets("Incidents").Range("F1:F65535") <= date1) * Application.WorksheetFunction.CountIf(Sheets("Requests").Range("C:C"), "Marcus Comins")
or some reason I cant translate the following formula into DB.. If i Could then it would be great. =SUMPRODUCT(--(Requests!H1:H65535<=DATE(2012,4,31))*(Requests!D1:D65535="Marcus Comins"))
I would appreciate any tips or suggestions to improve my technique.

Thank you.

Bob Phillips
04-19-2012, 03:22 PM
Can you post the workbook?

Aramazd
04-20-2012, 01:28 AM
Its Posted.

I know ive done some things very wrong especially the decleartion of the variables and the repeteation but this is so it can get me started.

Much appreciated for your help.


'Name: Aram Andreasyan
'Last Edited 20 April 2012
'Version 0.1


Public Sub OnLoad()
'On load make the chart visibility set to false
ChartObjects("chart 5").Visible = False
'Sets default value in ComboBoxes
ComboBoxMonthsIncidents = "ALL"
ComboBoxMonthsIncidents = "ALL"
'tickbox colors
chkbxAssignedToRequests.BackColor = &H800000
chkbxOpenedByRequests.BackColor = &H800000
chkbxResolvedByRequests.BackColor = &H800000
chkbxCallerRequests.BackColor = &H800000
chkbxAssignedToIncidents.BackColor = &H800000
chkbxOpenedByIncidents.BackColor = &H800000
'tickbox text formats
chkbxAssignedToRequests.ForeColor = &HFFFFFF
chkbxOpenedByRequests.ForeColor = &HFFFFFF
chkbxResolvedByRequests.ForeColor = &HFFFFFF
chkbxCallerRequests.ForeColor = &HFFFFFF
chkbxAssignedToIncidents.ForeColor = &HFFFFFF
chkbxOpenedByIncidents.ForeColor = &HFFFFFF
'button colors
btnCalculateIncidents.BackColor = &H8000000F
btnCalculateIncidents.ForeColor = &H0&
End Sub

Private Sub btnCalculateIncidents_Click()
'Since each partifular cell has a different condition it requires individual variables for every one of them.
'Using arrays could simplify the complexity of the code however I find this method to be easier to understand
'decleration of variables for each member of the UK-Oracle-Financials Team
Dim iIncidentsMC As Integer
Dim iIncidentsMS As Integer
Dim iIncidentsMD As Integer
Dim iIncidentsJH As Integer
Dim iIncidentsMO As Integer
Dim iIncidentsMP As Integer
Dim iIncidentsRR As Integer
Dim iIncidentsAT As Integer
Dim iIncidentsUJ As Integer
'Constructor which sets the variable value to 0 for each declared Integer
iIncidentsMC = 0
iIncidentsMS = 0
iIncidentsMD = 0
iIncidentsJH = 0
iIncidentsMO = 0
iIncidentsMP = 0
iIncidentsRR = 0
iIncidentsAT = 0
iIncidentsUJ = 0
'Conditional statement to add the additional values to their appropriate variables if the checkbox "Assigned to" set to true (checked)
If chkbxAssignedToIncidents = True Then
'If All the monthes are selected on the combo box
iIncidentsMC = iIncidentsMC + Application.WorksheetFunction.CountIf(Sheets("Incidents").Range("C:C"), "Marcus Comins")
iIncidentsMS = iIncidentsMS + Application.WorksheetFunction.CountIf(Sheets("Incidents").Range("C:C"), "Mathew Stevens")
iIncidentsMD = iIncidentsMD + Application.WorksheetFunction.CountIf(Sheets("Incidents").Range("C:C"), "Mark Doyle")
iIncidentsJH = iIncidentsJH + Application.WorksheetFunction.CountIf(Sheets("Incidents").Range("C:C"), "Jonathan Hardwick")
iIncidentsMO = iIncidentsMO + Application.WorksheetFunction.CountIf(Sheets("Incidents").Range("C:C"), "Mary Owolabi")
iIncidentsMP = iIncidentsMP + Application.WorksheetFunction.CountIf(Sheets("Incidents").Range("C:C"), "Michael Patel")
iIncidentsRR = iIncidentsRR + Application.WorksheetFunction.CountIf(Sheets("Incidents").Range("C:C"), "Raman Ratnam")
iIncidentsAT = iIncidentsAT + Application.WorksheetFunction.CountIf(Sheets("Incidents").Range("C:C"), "Alison Trory")
iIncidentsUJ = iIncidentsUJ + Application.WorksheetFunction.CountIf(Sheets("Incidents").Range("C:C"), "Urvee Juneja")
End If
'Conditional statement to add the additional values to their appropriate variables if the checkbox "Opened by" set to true (checked)
If chkbxOpenedByIncidents = True Then
iIncidentsMC = iIncidentsMC + Application.WorksheetFunction.CountIf(Sheets("Incidents").Range("D:D"), "Marcus Comins")
iIncidentsMS = iIncidentsMS + Application.WorksheetFunction.CountIf(Sheets("Incidents").Range("D:D"), "Mathew Stevens")
iIncidentsMD = iIncidentsMD + Application.WorksheetFunction.CountIf(Sheets("Incidents").Range("D:D"), "Mark Doyle")
iIncidentsJH = iIncidentsJH + Application.WorksheetFunction.CountIf(Sheets("Incidents").Range("D:D"), "Jonathan Hardwick")
iIncidentsMO = iIncidentsMO + Application.WorksheetFunction.CountIf(Sheets("Incidents").Range("D:D"), "Mary Owolabi")
iIncidentsMP = iIncidentsMP + Application.WorksheetFunction.CountIf(Sheets("Incidents").Range("D:D"), "Michael Patel")
iIncidentsRR = iIncidentsRR + Application.WorksheetFunction.CountIf(Sheets("Incidents").Range("D:D"), "Raman Ratnam")
iIncidentsAT = iIncidentsAT + Application.WorksheetFunction.CountIf(Sheets("Incidents").Range("D:D"), "Alison Trory")
iIncidentsUJ = iIncidentsUJ + Application.WorksheetFunction.CountIf(Sheets("Incidents").Range("D:D"), "Urvee Juneja")
End If
'Setting the appropriate cells to match the values of the newly created variables
Worksheets("UK-Oracle-Financials").Range("D30").Value = iIncidentsMC
Worksheets("UK-Oracle-Financials").Range("E30").Value = iIncidentsMS
Worksheets("UK-Oracle-Financials").Range("F30").Value = iIncidentsMD
Worksheets("UK-Oracle-Financials").Range("G30").Value = iIncidentsJH
Worksheets("UK-Oracle-Financials").Range("H30").Value = iIncidentsMO
Worksheets("UK-Oracle-Financials").Range("I30").Value = iIncidentsMP
Worksheets("UK-Oracle-Financials").Range("J30").Value = iIncidentsRR
Worksheets("UK-Oracle-Financials").Range("K30").Value = iIncidentsAT
Worksheets("UK-Oracle-Financials").Range("L30").Value = iIncidentsUJ
'Calculating the total (adding all the variables to each other
Worksheets("UK-Oracle-Financials").Range("M30").Value = iIncidentsMC + iIncidentsMS + iIncidentsMD _
+ iIncidentsJH + iIncidentsMO + iIncidentsMP + iIncidentsRR + iIncidentsAT + iIncidentsUJ
'while the incident button is pressed, this makes sure that all the request tick boxes are set to false
chkbxCallerRequests = False
chkbxAssignedToRequests = False
chkbxOpenedByRequests = False
chkbxResolvedByRequests = False
'Changes the name of the chart depending on which button is pressed. In this case to something relative towards the Incidents.
With Worksheets("UK-Oracle-Financials").ChartObjects("chart 5").Chart
.HasTitle = True
.ChartTitle.Text = "Incidents"
End With
'If none of the parameters are selected to carry out the search the chart will be hidden
If chkbxAssignedToIncidents = False And chkbxOpenedByIncidents = False Then
'hide the chart
ChartObjects("chart 5").Visible = False
'message to fill in parameters
MsgBox ("Please assign search parameters")
'change the label color
chkbxAssignedToIncidents.BackColor = &HC0&
chkbxOpenedByIncidents.BackColor = &HC0&
Else
'make the chart visible
ChartObjects("chart 5").Visible = True
'change the label color
chkbxAssignedToIncidents.BackColor = &H800000
chkbxOpenedByIncidents.BackColor = &H800000
chkbxAssignedToRequests.BackColor = &H800000
chkbxOpenedByRequests.BackColor = &H800000
chkbxResolvedByRequests.BackColor = &H800000
chkbxCallerRequests.BackColor = &H800000
End If
End Sub

Private Sub btnCalculateRequests_Click()
'No need for documentation, same functions and variables are created for the request button.
Dim iRequestsMC As Integer
Dim iRequestsMS As Integer
Dim iRequestsMD As Integer
Dim iRequestsJH As Integer
Dim iRequestsMO As Integer
Dim iRequestsMP As Integer
Dim iRequestsRR As Integer
Dim iRequestsAT As Integer
Dim iRequestsUJ As Integer
iRequestsMC = 0
iRequestsMS = 0
iRequestsMD = 0
iRequestsJH = 0
iRequestsMO = 0
iRequestsMP = 0
iRequestsRR = 0
iRequestsAT = 0
iRequestsUJ = 0
'//iRequestsTest = Application.Evaluate("SUMPRODUCT(--(Requests!H1:H65535=DATEVALUE(""25/10/2011"")*(Requests!H1H65535=""Marcus Comins"")))")
'//iRequestsTest = iRequestsTest + Application.WroksheetFunction.SumProduct(--Sheets("Incidents").Range("F1:F65535") <= date1) * Application.WorksheetFunction.CountIf(Sheets("Requests").Range("C:C"), "Marcus Comins")
'//iRequestTest = Evaluate("=SUMPRODUCT(--(Requests!H1:H65535<=dateMarch2012End), (Requests!H1:H65535>=date*(Requests!D1:D65535="Marcus Comins")))]
'Dim Name As String
'Dim Date1 As Double
'Dim s As String
'Name = Worksheets("UK-Oracle-Financials").Range("D29").Value
'Date1 = Worksheets("Requests").Range("H12781").Value
's = "SUMPRODUCT((Requests!C1:C65535 = """ & Name & """)*(Requests!H1:H65535= " & Date1 & "))"
's = Application.SumProduct((Sheets("Requests").Range("H1:H65535") = " & Date1 & ") * (Sheets(Requests).Range("C1:C65535") = """ & Name & """))
'Application.Evaluate (s)
'Worksheets("UK-Oracle-Financials").Range("L23").Value = s
'Worksheets("UK-Oracle-Financials").Range("L25").Value = iRequestsTest
If chkbxCallerRequests = True Then
iRequestsMC = iRequestsMC + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("C:C"), "Marcus Comins")
iRequestsMS = iRequestsMS + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("C:C"), "Mathew Stevens")
iRequestsMD = iRequestsMD + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("C:C"), "Mark Doyle")
iRequestsJH = iRequestsJH + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("C:C"), "Jonathan Hardwick")
iRequestsMO = iRequestsMO + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("C:C"), "Mary Owolabi")
iRequestsMP = iRequestsMP + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("C:C"), "Michael Patel")
iRequestsRR = iRequestsRR + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("C:C"), "Raman Ratnam")
iRequestsAT = iRequestsAT + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("C:C"), "Alison Trory")
iRequestsRR = iRequestsRR + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("C:C"), "Raman Ratnam")
iRequestsUJ = iRequestsUJ + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("C:C"), "Urvee Juneja")
End If
If chkbxAssignedToRequests = True Then
iRequestsMC = iRequestsMC + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("D:D"), "Marcus Comins")
iRequestsMS = iRequestsMS + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("D:D"), "Mathew Stevens")
iRequestsMD = iRequestsMD + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("D:D"), "Mark Doyle")
iRequestsJH = iRequestsJH + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("D:D"), "Jonathan Hardwick")
iRequestsMO = iRequestsMO + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("D:D"), "Mary Owolabi")
iRequestsMP = iRequestsMP + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("D:D"), "Michael Patel")
iRequestsRR = iRequestsRR + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("D:D"), "Raman Ratnam")
iRequestsAT = iRequestsAT + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("D:D"), "Alison Trory")
iRequestsRR = iRequestsRR + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("D:D"), "Raman Ratnam")
iRequestsUJ = iRequestsUJ + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("D:D"), "Urvee Juneja")
End If
If chkbxOpenedByRequests = True Then
iRequestsMC = iRequestsMC + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("E:E"), "Marcus Comins")
iRequestsMS = iRequestsMS + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("E:E"), "Mathew Stevens")
iRequestsMD = iRequestsMD + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("E:E"), "Mark Doyle")
iRequestsJH = iRequestsJH + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("E:E"), "Jonathan Hardwick")
iRequestsMO = iRequestsMO + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("E:E"), "Mary Owolabi")
iRequestsMP = iRequestsMP + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("E:E"), "Michael Patel")
iRequestsRR = iRequestsRR + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("E:E"), "Raman Ratnam")
iRequestsAT = iRequestsAT + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("E:E"), "Alison Trory")
iRequestsRR = iRequestsRR + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("E:E"), "Raman Ratnam")
iRequestsUJ = iRequestsUJ + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("E:E"), "Urvee Juneja")
End If
If chkbxResolvedByRequests = True Then
iRequestsMC = iRequestsMC + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("F:F"), "Marcus Comins")
iRequestsMS = iRequestsMS + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("F:F"), "Mathew Stevens")
iRequestsMD = iRequestsMD + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("F:F"), "Mark Doyle")
iRequestsJH = iRequestsJH + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("F:F"), "Jonathan Hardwick")
iRequestsMO = iRequestsMO + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("F:F"), "Mary Owolabi")
iRequestsMP = iRequestsMP + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("F:F"), "Michael Patel")
iRequestsRR = iRequestsRR + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("F:F"), "Raman Ratnam")
iRequestsAT = iRequestsAT + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("F:F"), "Alison Trory")
iRequestsRR = iRequestsRR + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("F:F"), "Raman Ratnam")
iRequestsUJ = iRequestsUJ + Application.WorksheetFunction.CountIf(Sheets("Requests").Range("F:F"), "Urvee Juneja")
End If
Worksheets("UK-Oracle-Financials").Range("D30").Value = iRequestsMC
Worksheets("UK-Oracle-Financials").Range("E30").Value = iRequestsMS
Worksheets("UK-Oracle-Financials").Range("F30").Value = iRequestsMD
Worksheets("UK-Oracle-Financials").Range("G30").Value = iRequestsJH
Worksheets("UK-Oracle-Financials").Range("H30").Value = iRequestsMO
Worksheets("UK-Oracle-Financials").Range("I30").Value = iRequestsMP
Worksheets("UK-Oracle-Financials").Range("J30").Value = iRequestsRR
Worksheets("UK-Oracle-Financials").Range("K30").Value = iRequestsAT
Worksheets("UK-Oracle-Financials").Range("L30").Value = iRequestsUJ
Worksheets("UK-Oracle-Financials").Range("M30").Value = iRequestsMC + iRequestsMS + iRequestsMD _
+ iRequestsJH + iRequestsMO + iRequestsMP + iRequestsRR + iRequestsAT + iRequestsUJ
chkbxAssignedToIncidents = False
chkbxOpenedByIncidents = False
ChartObjects("chart 5").Visible = True
'Chart
With Worksheets("UK-Oracle-Financials").ChartObjects("chart 5").Chart
.HasTitle = True
.ChartTitle.Text = "Requests"
End With
'If none of the parameters are selected to carry out the search the chart will be hidden
If chkbxAssignedToRequests = False And chkbxOpenedByRequests = False And chkbxResolvedByRequests _
= False And chkbxCallerRequests = False Then
'hide the chart
ChartObjects("chart 5").Visible = False
'Message the user to enter criteria
MsgBox ("Please assign search parameters")
'set the tickboxes to red to notify that they need to be checked
chkbxAssignedToRequests.BackColor = &HC0&
chkbxOpenedByRequests.BackColor = &HC0&
chkbxResolvedByRequests.BackColor = &HC0&
chkbxCallerRequests.BackColor = &HC0&
Else
'make the chart visible
ChartObjects("chart 5").Visible = True
'tickbox Colors
chkbxAssignedToRequests.BackColor = &H800000
chkbxOpenedByRequests.BackColor = &H800000
chkbxResolvedByRequests.BackColor = &H800000
chkbxCallerRequests.BackColor = &H800000
chkbxAssignedToIncidents.BackColor = &H800000
chkbxOpenedByIncidents.BackColor = &H800000
End If
End Sub

Private Sub btnOpenMainMenuFromOracleFinancials_Click()
'Go back to menu
Sheet8.Activate
End Sub

Private Sub btnResetStats_Click()
'Resets the stats on the table
'Replaces every cell value with 0
'Makes sure all the tick boxes are false
'Sets chart to be invisible
Worksheets("UK-Oracle-Financials").Range("D30:M30").Value = 0
chkbxCallerRequests = False
chkbxAssignedToRequests = False
chkbxOpenedByRequests = False
chkbxResolvedByRequests = False
chkbxAssignedToIncidents = False
chkbxOpenedByIncidents = False
ChartObjects("chart 5").Visible = False
'Clears the content in ComboBoxes
ComboBoxMonthsIncidents = "ALL"
ComboBoxMonthsRequests = "ALL"
End Sub

Private Sub ComboBoxMonthsIncidents_Change()
'Array of Months
ComboBoxMonthsIncidents.List = Array("April 2012", "March 2012", "February 2012", "January 2012", "December 2011", "November 2011", _
"October 2011", "September 2011", "August 2011", "July 2011", "June 2011", "May 2011", "April 2011", "March 2011", "February 2011", _
"January 2011", "December 2010", "November 2010", "October 2010", "September 2010")
End Sub

Private Sub ComboBoxMonthsRequests_Change()
'Array of Months
ComboBoxMonthsRequests.List = Array("April 2012", "March 2012", "February 2012", "January 2012", "December 2011", "November 2011", _
"October 2011", "September 2011", "August 2011", "July 2011", "June 2011", "May 2011", "April 2011", "March 2011", "February 2011", _
"January 2011", "December 2010", "November 2010", "October 2010", "September 2010")
End Sub

Bob Phillips
04-20-2012, 02:42 AM
No, that is the code, not the workbook. The data is as meaningful as the code.

Aramazd
04-20-2012, 02:52 AM
I cant attach the file I get the following error. Can I send you through email by any chance?

Error: "Invalid Post specified. If you followed a valid link, please notify the administrator"

Bob Phillips
04-20-2012, 04:42 AM
Post it to one of the file sharing sites, YouSendIt, DropBx, SkyDrive and so on.

Aramazd
04-20-2012, 01:53 PM
www<dot>2shared<dot>com/file/SX14aYYF/Try2.html

Thanks in advance

Aramazd
04-22-2012, 03:27 AM
p.s not allowed to post links since im new to this forum :)

Bob Phillips
04-23-2012, 12:08 AM
Couldn't open it, never got past 96%.

Aramazd
04-23-2012, 01:23 AM
Just message me your email so I can send it to you via outlook. Im at work all those upload sites are blocked.

Thanks.

Aramazd
04-24-2012, 05:55 AM
Guys I really need help on this please someone respond.

Aramazd
04-24-2012, 06:22 AM
Is there no way this following formula can be translated instead?

=SUMPRODUCT(--(Requests!H1:H65535<=DATE(2012,4,31))*(--(Requests!H1:H65535>=DATE(2012,4,1))*(Requests!D1:D65535="Employee Name")))

Bob Phillips
04-24-2012, 06:24 AM
Have you tried this?

iRequestTest = Evaluate("=SUMPRODUCT(--(Requests!H1:H65535<=dateMarch2012End), _
(Requests!H1:H65535>=date)*(Requests!D1:D65535=""Marcus Comins""))")

Aramazd
04-24-2012, 06:55 AM
Thanks for the reply.

I receive the run time error 13 missmatch error for this following code

Dim d1 As Date
Dim d2 As Date

d2 = DateValue("April 31, 2012")
d1 = DateValue("April 1, 2012")



iRequestTest = Evaluate("=SUMPRODUCT(--(Requests!H1:H65535<=d2),(Requests!H1:H65535>=d1)*(Requests!D1:D65535=""Marcus Comins""))")

Worksheets("UK-Oracle-Financials").Range("I35").Value = iRequestTest

Bob Phillips
04-24-2012, 07:08 AM
How about this

Dim d1 As Date
Dim d2 As Date

d2 = DateValue("2012-Apr-13")
d1 = DateValue("2012-Apr-01")

iRequestTest = Evaluate("=SUMPRODUCT(--(Requests!H1:H65535<=--""" & Format(d2, "yyyy-mm-dd") & """)," & _
"--(Requests!H1:H65535>=--""" & Format(d1, "yyyy-mm-dd") & """)," & _
"--(Requests!D165535=""Mar cus Comins""))")
Worksheets("UK-Oracle-Financials").Range("I35").Value = iRequestTest

Aramazd
04-24-2012, 07:11 AM
Apologies im a compelte idiot didnt think that vba would catch on april having 30 days instead of 31.

It compiles however I get a #NAME?as a result.

Heres the code

Dim d1 As Date
Dim d2 As Date

aprilStart = DateValue("April 1, 2012")
aprilEnd = DateValue("April 30, 2012")




iRequestTest = Evaluate("=SUMPRODUCT(--(Requests!H1:H65535<=aprilEnd),(Requests!H1:H65535>=AprilStart)*(Requests!D1:D65535=""Marcus Comins""))")

Worksheets("UK-Oracle-Financials").Range("I35").Value = iRequestTest

Do I need to convert it into a string or int?

Aramazd
04-24-2012, 07:17 AM
THATS IT IT WORKS :)
Thank you!! thank you!!
Ive been busting my head on this syntax for a week now.