formulas in a useform

[ashgull80]

hi is it possible to have a sumproduct formula in a userform?
if so how would this formula be displayed;
=sumproduct(--(year(incomeOv!$c$7:$c419989)=2008),incomeOv!$g$7:$g$19989)
thanks ash

[Bob Phillips]

You have to force Excel to evaluate it



Activesheet.Evaluate("=sumproduct(--(year(incomeOv!$c$7:$c419989)=2008),incomeOv!$g$7:$g$19989)")

[ashgull80]

so if i wish a textbox to display the result doi just put 'textbox1.value' in from of what you said?
also is it possible to change the 2008 to 'textbox2.value' so when the text box2 value is changed textbox1 will display new results?
thank you
ash

[ashgull80]

i have put into a userform like this.
textbox1.value = Activesheet.Evaluate("=sumproduct(--(year(incomeOv!$c$7:$c419989)=2008),incomeOv!$g$7:$g$19989)")
but i wish to change the 2008 to textbox2 so the range can be changed.
is this possible?
thanks

[Bob Phillips]

textbox1.value = Activesheet.Evaluate("=sumproduct(--(year(incomeOv!$c$7:$c419989)=" & Val(TextBox2.Text) & "),incomeOv!$g$7:$g$19989)")

[ashgull80]

thank you

[ashgull80]

how can i divide 2 formulas?
("=sumproduct(--(year(incomeOv!$c$7:$c419989)=" & Val(TextBox2.Text) & "),incomeOv!$g$7:$g$19989)")
divided by
("=sumproduct(--(year(incomeOv!$c$7:$c419989)=" & Val(TextBox2.Text) & "),incomeOv!$J$7:$J$19989)")

[Bob Phillips]

Activesheet.Evaluate("=sumproduct(--(year(incomeOv!$c$7:$c419989)=2008),incomeOv!$g$7:$g$19989)") /
Activesheet.Evaluate("=sumproduct(--(year(incomeOv!$c$7:$c419989)=2008),incomeOv!$j$7:$j$19989)")

[Bob Phillips]

OR EVEN

Activesheet.Evaluate("=sumproduct(--(year(incomeOv!$c$7:$c419989)=2008),incomeOv!$g$7:$g$19989/incomeOv!$J$7:$J$19989)")

[ashgull80]

the 1st returns an overflow error the 2nd a type mismatch

[mikerickson]

In testing was the extra digit removed?

incomeOv!$c$7:$c419989

[Bob Phillips]

or even added?

[ashgull80]

not there at all, does it need 2 b? hadnt noticed it!

[Bob Phillips]

No it shouldn't be, making the ranges different sizes will cause SP to fail.

[ashgull80]

this is the formula i have used
textbox1.value = Activesheet.Evaluate("=sumproduct(--(year(incomeOv!$c$7:$c19989)=" & Val (txtyr.Text) & "),incomeOv!$g$7:$g$19989 / incomeOv!$J$7:$J$19989)")
and it returns - could not set the value property. type mismatch

[ashgull80]

any ideas on why im recieving this error?

[mdmackillop]

not there at all, does it need 2 b? hadnt noticed it!
Hi ASsgull
Please don't use text abbreviations when posting.
A: I hate them
B: How do you expect searches to work?

[ashgull80]

sorry bit harsh calling me an 'ass' though!

[mdmackillop]

:haha: A genuine typo!

[ashgull80]

i sure do hope so :friends:
dont suppose you know why im getting this error do you?

this is the formula i have used
textbox1.value = Activesheet.Evaluate("=sumproduct(--(year(incomeOv!$c$7:$c19989)=" & Val (txtyr.Text) & "),incomeOv!$g$7:$g$19989 / incomeOv!$J$7:$J$19989)")
and it returns - could not set the value property. type mismatch

[Bob Phillips]

Might be blanks in column J. Try



TextBox1.Value = ActiveSheet.Evaluate( _
"=SUM(IF((YEAR(incomeOv!$C$7:$C19989)=" & txtYr.Text & ")*" & _
"(incomeOv!$J$7:$J$19989<>""""),incomeOv!$G$7:$G$19989/incomeOv!$J$7:$J$19989))")

[ashgull80]

that is just the ticket thanks very much xlp