Solved

Translating a Cell SumProduct to a Userform SumProduct

Posted on 2009-07-08
8
472 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

920 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

15 Experts available now in Live!

Get 1:1 Help Now