Array computation in vba

[Sam]

I have a data table that has dates in column A and names in column B. If I
wanted to determine how many transactions an employee completed within a
given period, I'd use an array formula:
"=Sum(($A:$A>=StartDate)*($A:$A<=EndDate)*($B:$B=Employee)).

However I want to have a userform with a combo box for start date, another
for end date, another for employee. Then I want a label to display the number
of transactions the same as the Excel formula above. How can I code vba to
compute this?

Thanks,

Sam

 

[joel]

I wouldl use Sumproduct instead of SUM so yo don't have to worry abou
an array formula. If would use Evaluate to get the results into VBA.
used format to get the dates entered in the combobox in the same forma
as the worksheet.

Results
Evaluate("Sumproduct(($A:$A>=StartDate)*($A:$A<=EndDate)*($B:$B=Employee))")

You need to use variable in the above method so do this

StartDate = combobox1.text
StartDate = format(DateValue(StartDate,"MM/DD/YY"))
EndDateDate = combobox2.text
EndDate = format(DateValue(EndDate,"MM/DD/YY"))
Employee = Combobox3.text

Results = Evaluate("Sumproduct(($A:$A>=" & StartDate & ")*($A:$A<="
_
EndDate & ")*($B:$B=" & Employee & "))")


msgbox(Results)

 

[Rick Rothstein]

Assuming your controls have default names and that you are using a
CommandButton's Click event to execute your code, give this code a try...

Private Sub CommandButton1_Click()
Dim X As Long, LastRow As Long, Transactions As Double
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For X = 1 To LastRow
With Worksheets("Sheet1")
If .Cells(X, "A").Value >= CDate(ComboBox1.Value) And _
.Cells(X, "A").Value <= CDate(ComboBox2.Value) And _
.Cells(X, "B").Value = ComboBox3.Value Then
Transactions = Transactions + 1
End If
End With
Next
Label1.Caption = Transactions
End Sub