Solved

Translating a Cell SumProduct to a Userform SumProduct

Posted on 2009-07-08
8
469 Views
Last Modified: 2012-05-07
I am trying to take a SumProduct on a cell that works correctly, and translate it to a user form with a 3 textboxs(instead of the three cells).  No matter what i try i get a "Unable to get the SumProduct property of the WorksheetFunction class".  When i type in the code, the intellisense pops up and i actually pick it from there.  I cannot figure out what step i am missing.  I have tried other functions as well (Vlookup, SumIf) and get the same error.  I've used Application.WorkSheetFunction and WorkSheetFunction with the same result.
Code below works in a cell on the sheet (i never could get the syntax right to make it work with a named range)  


=SUMPRODUCT(SUMIF('Prize List'!$A$6:$E$69|Z13:AB13|'Prize List'!$B$6:$B$69))

Open in new window

0
Comment
Question by:DonnaOsburn
  • 4
  • 4
8 Comments
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 24805224
Use this way...
Saurabh...

Application.Evaluate ("SUMPRODUCT(SUMIF('Prize List'!$A$6:$E$69|Z13:AB13|'Prize List'!$B$6:$B$69))")

Open in new window

0
 

Author Comment

by:DonnaOsburn
ID: 24805601
Cells Z13, AA13, and AB13 are now textboxes on a user form called txtPrize1, txtPrize2, txtPrize3.
How can i use the SumProduct with the value of three textboxes instead of the cells.
Just putting that line in a get a type mismatch.
Is the result supposed to be dim as string, integer, long???
 
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 24806375
Can you upload your sample file as im not sure about what you are looking for...
0
 

Author Comment

by:DonnaOsburn
ID: 24806527

There is a sheet called 'Prize List'
Prize List is a spread sheet containing all of the prizes that can be selected.
Range A6:E69
Column A is lookup number
Column B is money amount to return
Column C-E are not used at this point.
Three text boxes on a user form
txtprize1, txtprize2, and txtprize3
i simply want to know how to take the value that the user enters in the txtprize1 text box (which is in Column A) then lookup the corresponding money in the B column then add that to the answer in the other two text boxes to allow me to show total money on the userform.

A     B    C    D   E
1    $50
2   $50
3  $100
4  $150
etc.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 24806639
and these textbox are part of userform..??
0
 

Author Comment

by:DonnaOsburn
ID: 24806718
yes
0
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 250 total points
ID: 24808333
Well to get a textbox value in the userform, You can simply do this assuming the textbox name is textbox1 then this will give you the value for textbox1 which is this..
textbox1.value
and the above statement will become...
 

Dim i As Long

i = Application.Evaluate("SUMPRODUCT(SUMIF('Prize List'!$A$6:$E$69," & textbox1.Value & ",'Prize List'!$B$6:$B$69))")

Open in new window

0
 

Author Closing Comment

by:DonnaOsburn
ID: 31601183
I would like to have been able to use the named range but this worked. Thank you.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now