Solved

Translating a Cell SumProduct to a Userform SumProduct

Posted on 2009-07-08
8
483 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 IT Pros

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

My experience with Windows 10 over a one year period and suggestions for smooth operation
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

734 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