DonnaOsburn
asked on
Translating a Cell SumProduct to a Userform SumProduct
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.WorkSheetFunct ion 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)
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))
ASKER
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???
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???
Can you upload your sample file as im not sure about what you are looking for...
ASKER
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.
and these textbox are part of userform..??
ASKER
yes
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I would like to have been able to use the named range but this worked. Thank you.
Saurabh...
Open in new window