Sum If in a User Form

[Gingermouse]

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

[Norie]

Where do you get the error?

Try this for the ranges.

With Sheets("Sheet1")

   Set Rng1 =.Range("A2", .Range("A" & Rows.Count).End(xlUp))
   Set Rng2 =.Range("C2", .Range("C"  & Rows.Count).End(xlUp))
End With

You can also try this for the combobox.

 Me.ComboBox1.List = Evaluate("TRANSPOSE(ROW(1:52))")
  
 Me.ComboBox1.AddItem "*.*", 0
 Me.ComboBox1 = "*.*"

[Norie]

Just rembered.

Please add code tags.

Posting code in [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

[Gingermouse]

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

[Gingermouse]

OK,

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

[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]

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

[Norie]

GM

That code is run when the userform is opened, not when you select a value from the combobox.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

PS You almost got the code tags, not quite though - just select the code and press #, that's the easiest way to add them.

[Gingermouse]

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

[Norie]

Nothing happens when you select from the first combobox because there's no code to be triggered.

The 0 you see in the second combobox is a result of the code in the Initialize event which is running this SUMIF.

SUMIF($A$10, "*.*", $C$10)

[Gingermouse]

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

It is as follows:

Private Sub ComboBox1_Change()

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

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


Me.ComboBox2 = smif

End Sub

Private Sub UserForm_Initialize()






For i = 1 To 52

Me.ComboBox1.AddItem (i)

Next i

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




End Sub

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

[Norie]

You aren't setting Rng1 and Rng2 properly.

This,

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

is the same as this,

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

So Rng1 is set to the last cell in column A, Rng2 to the last cell in column C.

Try putting End(xlDown) inside the parentheses, immediately after the 2nd Range("A2").

There's another problem, the values on the worksheet are numbers but the values in the combobox are text.

What you need to do there is convert the combobox value in the SumIf to a number, have a look at VBA's conversion functions.

[Gingermouse]

Ok,

Thanks. That gives me some direction to take.

Cheers

GM

[Norie]

There's another thing concerning the ranges.

You set them in the Initialize event, they won't be recognised outside that sub.

[Gingermouse]

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

[Norie]

Glad you got it sorted.

Wasn't sure about the number/text thing, sometimes that does cause problems though it's seems to be mostly with lookups.