Filter months and name

[iggydarsa]

Hi everyone,

I am trying to use filter options with VBA, I appretiate any help.

I have a sheet (Sheet1) with information of:

Date.....ProductName....Price....Cost
1-Mar.......ProdA...........10.........7
1-Mar.......ProdA...........10.........7
3-Mar.......ProdB...........12.........7
4-Apr........ProdA...........11........6
7-Apr........ProdB...........14........6 etc...

I am trying to create a userform that will have

1- Begining Month combobox (cbBeginMonth): I don't know how to populate the name of the months without writing it somewhere on the sheet and assign it to the RowSource.

2- Ending Month combobox (cbEndMonth): As I mentioned above, how can I populate the names of the months without typing them somewhere on the sheet?

3- Product Name combobox (cbProdName): No problem with this one, I can populate it.

Lets say someone chose "March" from the cbBeginMonth, "October" from cbEndMonth and "ProdA" from the cbProdName
I would like to see the subtotals of Price and Cost of ProdA within the dates of 1-Mar and 31-Oct.

PS: Even though the date format on the sheet is "4-Apr", I just want to see "April" on the comboboxes.

I hope I was clear enough
and again, I appretiate any help

Thanks

 

[acw]

Hi

1) to add the months try adding this to the form initialize

Private Sub UserForm_Initialize()
For i = 1 To 12
 cbBeginMonth.AddItem Format("1/" & i, "mmmm")
 cbEndMonth.AddItem Format("1/" & i, "mmmm")
Next i

End Sub

2) For the outputs, I've assumed there are 2 textboxes to take the output and there is a command button to initiate. This is the code for the command button

Private Sub CommandButton1_Click()
lastrow = Sheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row
TextBox1.Value = Evaluate("=SUMPRODUCT(--(MONTH(A2:A" & lastrow & ")>=MONTH(""1/" & cbBeginMonth.Value & """)),--(MONTH(A2:A" & lastrow & ")<=MONTH(""1/" & cbEndMonth.Value & """)),--(B2:B" & lastrow & "=""" & cbProdName.Value & """),(C2:C" & lastrow & "))")
TextBox2.Value = Evaluate("=SUMPRODUCT(--(MONTH(A2:A" & lastrow & ")>=MONTH(""1/" & cbBeginMonth.Value & """)),--(MONTH(A2:A" & lastrow & ")<=MONTH(""1/" & cbEndMonth.Value & """)),--(B2:B" & lastrow & "=""" & cbProdName.Value & """),(D2:D" & lastrow & "))")
End Sub

HTH

Tony

 

[iggydarsa]

Thanx Tony,

Beacuse I am on another computer I couldn't try your solution YET, but populating the comboboxes with month names looks about right.

However, I have a question about SUMPRODUCT part of your answer. What is the reason for "--" before the MONTH funtion. What purpose does it serve?

Thanks again
Iggy

 

[acw]

Iggy

It converts the true/false result into a number. THe first - will make it negative, the second will make it positive again. This way you are multiplying numbers in the sumproduct function.


Tony

 

[iggydarsa]

I think, i got it
thx