I have a spreadsheet with columns size,date, number, and amount.
I've created a userform with two sets of option buttons, one which has small medium and large, and the other has the variable to analyze, or number or amount. There is also a list box with the 12 months of the year.
How do I calculate, for example, the average amount (in $) for the small shirts sold in january, given that those are the options selected in my user form?
A sample workbook would help, but I faked 3 months of data in the attachment
Something like thing might give you some ideas
There are faster ways (SUMPRODUCT for example) but this is simpler, and has almost NO error checking
Mostly I wasn't sure how consistent you data is
Code:Option Explicit
Private Sub cbGo_Click()
Dim rData As Range
Dim iRow As Long
Dim iTotalCount As Long
Dim dblTotalAmount As Double
Dim iMonthNumber As Long
iTotalCount = 0
dblTotalAmount = 0#
iMonthNumber = Me.lbMonths.ListIndex + 1
Set rData = Worksheets("Sheet1").Cells(1, 1).CurrentRegion
For iRow = 2 To rData.Rows.Count
With rData.Rows(iRow)
If Me.obSmall.Value And LCase(.Cells(1).Value) = "small" Then
If Month(.Cells(2).Value) = iMonthNumber Then
iTotalCount = iTotalCount + 1
dblTotalAmount = dblTotalAmount + .Cells(4).Value
End If
ElseIf Me.obMedium.Value And LCase(.Cells(1).Value) = "medium" Then
If Month(.Cells(2).Value) = iMonthNumber Then
iTotalCount = iTotalCount + 1
dblTotalAmount = dblTotalAmount + .Cells(4).Value
End If
ElseIf Me.obLarge.Value And LCase(.Cells(1).Value) = "large" Then
If Month(.Cells(2).Value) = iMonthNumber Then
iTotalCount = iTotalCount + 1
dblTotalAmount = dblTotalAmount + .Cells(4).Value
End If
End If
End With
Next iRow
MsgBox "Total Count = " & iTotalCount
MsgBox "Total Amount = " & dblTotalAmount
If dblTotalAmount > 0# Then
MsgBox "Average = " & (dblTotalAmount) / iTotalCount
End If
End Sub
Private Sub UserForm_Initialize()
Me.obSmall.Value = True
Me.lbMonths.AddItem "Jan"
Me.lbMonths.AddItem "Feb"
Me.lbMonths.AddItem "Mar"
Me.lbMonths.AddItem "Apr"
Me.lbMonths.AddItem "May"
Me.lbMonths.AddItem "Jun"
Me.lbMonths.AddItem "Jul"
Me.lbMonths.AddItem "Aug"
Me.lbMonths.AddItem "Sep"
Me.lbMonths.AddItem "Oct"
Me.lbMonths.AddItem "Nov"
Me.lbMonths.AddItem "Dec"
End Sub
How can I upload my file so you can see?
Attachment 13189
Where 1 refers so small, 2 to medium, 3 to large
I tried taking yours and applying it to mine, but I keep getting invalid use of "Me" keyword and I'm not quite sure what that means or what to do to fix it.
I need to get the average number and amount for either one month or multiple months. I had it on mine where you can select multiple months from the user form.
I do appreciate your help though! I'm so lost...
This is apparently for a homework assignment, so you'll have to do a lot for yourself
We can answer questions and provide hints, but not more than that (no one wants to incur the wrath of a teacher)
1. The was no code to run when you click the execute button
2. You should use a CommandButton on the UF, not a ToggleButton
3. The statement assumes that the block of data starts in A1. Yours seems to start in A3
Code:Set rData = Worksheets("Sheet1").Cells(1, 1).CurrentRegion
4. On my small example, I renamed the UF controls to something meaningful (e.g. obSmall), instead of the generic OptionButton1. I don't think you made them compatible
5. My code looked for text "small" etc. not 1, 2 or 3
6. I'm guessing that the "Me" error was because I put initialization code into the UF (which would be the 'Me') and you didn't
7. I always like to use Option Explicit at the top of modules -- Online help has a good write up
Here's another example using your data, but you still have work to do:
It still only handles a single month, so you'll need to read the correct ListBox properties and do the math for each selected month.
The results are displayed as part of the UF, so you probably want to use Public variables in the standard module
A good way to see what's happening is to put a breakpoint on a line -- macro will run to that and stop, then you can single step through the rest of the code. I put a little picture in the example in case I'm not clear
Thank you so much! I'm running through it now and adjusting...
one thing though, when I select small and january I get a different number than if I manually average each small from january... I'm looking at the code and the logic/math seems appropriate, I can't figure out the discrepancy there.
The total count from the program is 11 when there's 14....
Attachment 13191I get 14