malik641
08-04-2005, 11:21 AM
I don't know where to start with this, but here goes nothing...
Okay, if you remember I have a macro function that looks into cell comments and adds up the number of NewHires (new hired people) and places them in a cell that called the function given the range specified. It also looked at employee terminations and did the same thing. Now I'm trying something new (in addition to those 2 functions).
In the attachment there are two tables (One with all departments listed with total values of new hires and terminations [transfers do not count] and the other table is ONLY for the terminations)
The first table works just the way it is supposed to. Where my problem lies is in the second table. In the second table displays which department had leavers (terminations) and how many for each department. This table is shorter than the first because some of the departments are combined and given a new name (i.e. dept numbers 151 + 152 + 153 + 154 can all be combined to have the name "Post Life"). The departments with combined numbers hold these numbers in a comment.
In the function "Leavers" that I have, I can collect the numbers of the departments that had terminations and how many were terminated. I placed them in a variant called deptNum (be sure the immediate window is open to see what I am talking about). I need to know (for now) if I can place this variant into an array at the end of the function (which I will use to match the one table's department numbers with the second table's)??
That's all I'm asking, cause I know that this is a LOT to do (at least for me, a novice). I figure if I can get that variant into an array I will be able to perform the rest of my task.
Take a look...and I know, I know I should "Redesign". But there is too much to go through with the REAL workbook that I have. Not to mention the users will have to get used to it and....sigh....it just goes on and on. So the time taken to do this macro will be less than "Redesigning" it.
But check it out:
Okay, if you remember I have a macro function that looks into cell comments and adds up the number of NewHires (new hired people) and places them in a cell that called the function given the range specified. It also looked at employee terminations and did the same thing. Now I'm trying something new (in addition to those 2 functions).
In the attachment there are two tables (One with all departments listed with total values of new hires and terminations [transfers do not count] and the other table is ONLY for the terminations)
The first table works just the way it is supposed to. Where my problem lies is in the second table. In the second table displays which department had leavers (terminations) and how many for each department. This table is shorter than the first because some of the departments are combined and given a new name (i.e. dept numbers 151 + 152 + 153 + 154 can all be combined to have the name "Post Life"). The departments with combined numbers hold these numbers in a comment.
In the function "Leavers" that I have, I can collect the numbers of the departments that had terminations and how many were terminated. I placed them in a variant called deptNum (be sure the immediate window is open to see what I am talking about). I need to know (for now) if I can place this variant into an array at the end of the function (which I will use to match the one table's department numbers with the second table's)??
That's all I'm asking, cause I know that this is a LOT to do (at least for me, a novice). I figure if I can get that variant into an array I will be able to perform the rest of my task.
Take a look...and I know, I know I should "Redesign". But there is too much to go through with the REAL workbook that I have. Not to mention the users will have to get used to it and....sigh....it just goes on and on. So the time taken to do this macro will be less than "Redesigning" it.
But check it out: