View Full Version : formulas in a useform
ashgull80
02-17-2008, 11:27 AM
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
02-17-2008, 11:32 AM
You have to force Excel to evaluate it
Activesheet.Evaluate("=sumproduct(--(year(incomeOv!$c$7:$c419989)=2008),incomeOv!$g$7:$g$19989)")
ashgull80
02-17-2008, 11:36 AM
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
02-17-2008, 01:33 PM
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
02-17-2008, 01:58 PM
textbox1.value = Activesheet.Evaluate("=sumproduct(--(year(incomeOv!$c$7:$c419989)=" & Val(TextBox2.Text) & "),incomeOv!$g$7:$g$19989)")
ashgull80
02-17-2008, 02:05 PM
thank you
ashgull80
02-17-2008, 04:15 PM
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
02-17-2008, 05:23 PM
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
02-17-2008, 05:24 PM
OR EVEN
Activesheet.Evaluate("=sumproduct(--(year(incomeOv!$c$7:$c419989)=2008),incomeOv!$g$7:$g$19989/incomeOv!$J$7:$J$19989)")
ashgull80
02-17-2008, 06:16 PM
the 1st returns an overflow error the 2nd a type mismatch
mikerickson
02-17-2008, 07:06 PM
In testing was the extra digit removed?
incomeOv!$c$7:$c419989
Bob Phillips
02-18-2008, 01:43 AM
or even added?
ashgull80
02-18-2008, 06:52 AM
not there at all, does it need 2 b? hadnt noticed it!
Bob Phillips
02-18-2008, 07:02 AM
No it shouldn't be, making the ranges different sizes will cause SP to fail.
ashgull80
02-18-2008, 08:00 AM
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
02-23-2008, 04:47 PM
any ideas on why im recieving this error?
mdmackillop
02-23-2008, 05:29 PM
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
02-23-2008, 05:34 PM
sorry bit harsh calling me an 'ass' though!
mdmackillop
02-23-2008, 05:36 PM
:haha: A genuine typo!
ashgull80
02-23-2008, 05:48 PM
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
02-24-2008, 02:32 AM
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
02-24-2008, 07:49 AM
that is just the ticket thanks very much xlp
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.