Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array computation in vba
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=E mployee)). 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array computation in vba
I wouldl use Sumproduct instead of SUM so yo don't have to worry about an array formula. If would use Evaluate to get the results into VBA. I used format to get the dates entered in the combobox in the same format as the worksheet. Results = Evaluate("Sumproduct(($A:$A=StartDate)*($A:$A<=En dDate)*($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) -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=195353 http://www.thecodecage.com/forumz |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array computation in vba
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 -- Rick (MVP - Excel) "Sam" wrote in message ... 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=E mployee)). 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Age computation | Excel Programming | |||
percentage computation | Excel Discussion (Misc queries) | |||
Date Computation | Excel Programming | |||
computation error | Excel Discussion (Misc queries) | |||
Rew Macro For Computation | Excel Programming |