Excel function: Sumproduct to match certain veriables with a date parameter

See more:

VB

VBA

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<br />
Column 3 = Date, DateFormat = "24/10/2011"

Heres 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 = iRequestTest

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")

For 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 tecnique.

Thank you.


My next step would be to allocate a new variable to Date1 with a case or condition based on the parameter selections from the combo box by the user. So if April is selected the Date1 variable will change and point its value somewhere else and etc.. Think this will minimise the complexity of the code.

Suggestions?

[EDIT]"Code" tags and text from comment added - LOSMAC[/EDIT]