Sum If in a User Form

Hi,

I am trying to make one combo box drive a sum if in a second combo box using the worksheet fuction Sumif.

For some reason I am getting the following error: Object required (Error 424).

My code is as follows:


Private Sub UserForm_Initialize()

Rng1 = Sheets("Sheet1").Range(("A2"), Range("A2")).End(xlDown)
Rng2 = Sheets("Sheet1").Range(("C2"), Range("C2")).End(xlDown)


For i = 1 To 52

Me.ComboBox1.AddItem (i)

Next i

Me.ComboBox1.AddItem "*.*"
Me.ComboBox1 = "*.*"


Smif = WorksheetFunction.SumIf(Rng1, i, Rng2)

Me.ComboBox2 = Smif

End Sub




If anyone can advise where I am gonig wrong I would be very greatful.

Cheers

GM

Hi,

Thanks for that the, though ranges and code for combo box1 work fine.

The bit I am having trouble with is getting the second combo box to calculate a sum if.

Regards

GM

OK,

Been reading around and I have managed to refine the code as follows:

Please Login or Register  to view this content.

[Private Sub UserForm_Initialize()

Set Rng1 = Sheets("Sheet1").Range(("A2"), Range("A2")).End(xlDown)
Set Rng2 = Sheets("Sheet1").Range(("C2"), Range("C2")).End(xlDown)

For i = 1 To 52
Me.ComboBox1.AddItem (i)
Next i
Me.ComboBox1.AddItem "*.*"
Me.ComboBox1 = "*.*"

Smif = Application.SumIf(Rng1, ComboBox1.Value, Rng2)

Me.ComboBox2 = Smif
End Sub]

Please Login or Register  to view this content.

The code runs, the problem I have now is that the sum if returns zero.For every value of combbox 1 selected.

If I recplace with hard coded ranges and a number to search then it works. I think it is something to do with passing arguments to the function and setting them as numbers, but not sure.

Can anyone advise to why this is happending and or a solution?

Cheers

GM

Parent Child Combo.xlsm

Hi,


Thanks.

I have attached the file. If you run usefrom1 it should be clear what i am trying to do.

I want the Parent box to drive the sum if and be returned in the Child box.


I will also think about what you said re the intialise issue

Thanks

GM

Hi Revised the code to be based on the correct event, i think.

It is as follows:

Please Login or Register  to view this content.

It still looks like the the inputs and ranges to the sumif function are not being recognised. As the form works if I hard code ranges and values.

Again thanks for looking at this

GM

Ok,

Thanks. That gives me some direction to take.

Cheers

GM

Hi,

I realised that with the ranges thanks and have moved them to the click event.

Also I moved the bracket for the ranges and it works perfectly.

As fir the combo box returning txt, thats good to know however the function seems to be able to use txt and number interchangeably as it now works.

Thanks for the guidance.

Much preferred than just code as this way i learn't something.

Cheers

GM