I currently have a spreadsheet that starts up with a series of userforms.
Within these userforms an employee will select certain criteria to base
equations on. I've built all of the dialog boxes and everything works great.
The issue I'm having now is that when the employee hits the final command
button I want an equation to populate. The current example is very simple
and only has one requirement. I originally wrote the equation in excel to
first demonstrate that using several SUMIFS we could accomplish what we
needed. I was then told that there could be many different criteria and I
would prefer to have excel only calculate one equation instead of many if
they only needed one at a time. Below is the current excel equation.
=((SUMIFS('recovery idea.xlsm'!PAID2003,'recovery
idea.xlsm'!DAYS2003,">="&$A2,'recovery idea.xlsm'!DAYS2003,"<="&$B2,'recovery
idea.xlsm'!DTEOFPAY2003,"<="&'recovery idea.xlsm'!MAXDATE))+(SUMIFS('recovery
idea.xlsm'!PAID2007,'recovery idea.xlsm'!DAYS2007,">"&$A2,'recovery
idea.xlsm'!DAYS2007,"<="&$B2,'recovery
idea.xlsm'!DTEOFPAY2007,"<="&MAXDATE)))/(SUMIF(REFERREDDTE,"<"&MAXDATE-RecoveryRates!$A2,BALREFERRED))
A2 currently equals 0 and B2 equals 30. A3 would equal 31 and B3 60... so
on and so forth. The first two SUMIFS gather amounts that meet the criteria
(in this case any payment made prior to the MAXDATE, the max date is being
pulled from the last dialog box). The third SUMIF divides the total by the
amount that was referred. Basically showing the amount collected over the
total possible.
Below is the current code for when the command button is hit.
Private Sub CommandButton2_Click()
Sheet4.Cells(2, "H") = TextBox1.Value
Unload AllLegalNone
End Sub
Thanks!
|
Similar Threads
