?
Solved

Translating a Cell SumProduct to a Userform SumProduct

Posted on 2009-07-08
8
Medium Priority
?
485 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

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
 
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 750 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

764 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