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:

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?





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
Label1.Caption = Transactions
End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question