Reference Edit Control

Posted by tim on January 22, 2002 7:28 AM

I can't find a control that will allow the user to change a referenced range of cells. I see one in a VC program Example and it looks like the control is called RefEdit, but it is not in my Control Box in VBA. Is this it? Do I have to add it in or get it from somewhere else?

Posted by Juan Pablo G. on January 22, 2002 7:37 AM

Let's say that the start dates are in cells B2:B28 and the end dates are in cells C2:C28 (I know you have more, but this is just an example). Either on the same sheet or on another sheet, fill in 13 rows with dates that are all the first of the month. For example, I want to know the number of employees for each month of 2001, so I have the following in my column:

01/01/01
02/01/01
03/01/01
04/01/01
05/01/01
06/01/01
07/01/01
08/01/01
09/01/01
10/01/01
11/01/01
12/01/01
01/01/02

(You need one more month than what you are calculating for).

Say those dates are in cells K2:K14. In L2 type the following formula:

=SUMPRODUCT(($B$2:$B$28&LT;K3)*($C$2:$C$28>=K2)*$D$2:$D$28)

Replace your actual ranges/cells and then pull the formula down to L13.

Hope this helps,

Russell

Posted by Juan Pablo G. on January 22, 2002 7:39 AM

This appears when you insert a UserForm

Juan Pablo G.