Solved

Translating a Cell SumProduct to a Userform SumProduct

Posted on 2009-07-08
8
476 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

821 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