Prev: If conditions for Data Entry Form
Next: Adding ws information to cell in new wb & saving with ws name
From: Sam on 13 Apr 2010 15:56
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
From: Rick Rothstein on 13 Apr 2010 17:19
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" <Sam(a)discussions.microsoft.com> wrote in message
news:B5B088E3-0453-4570-8152-5A8FFB928069(a)microsoft.com...
> 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

From: joel on 13 Apr 2010 17:16

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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=195353

http://www.thecodecage.com/forumz

 | 
Pages: 1
Prev: If conditions for Data Entry Form
Next: Adding ws information to cell in new wb & saving with ws name