PDA

View Full Version : Sleeper: VBA array questions...BIG project



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:

acw
08-04-2005, 10:42 PM
Joseph

This is a bit cumbersome but you could build a couple of arrays - one that houses the department number and the other that houses the count. If you do it this way, then you could do a match on the department array for the department number and if it is found, then extract the count from the count array.

Put an extra optional variable in the Leavers function. This will house the department number. If it is missing, then it will return the total count as it presently does. If the variable is present, then you can do a search on the department array, and if it is found then extract the number from the count array.

The function in C71 would be as it stands.

However, if you put it into C50, it would be =Leavers(C2:C44,B50)

Hopefully the following will give you an idea of what I mean


Sub ccc()
arrdept = Array(111, 132)
arrcount = Array(1, 2)
On Error Resume Next
holder = WorksheetFunction.Match(132, arrdept, 0)
If holder <> "" Then MsgBox arrcount(holder - 1)
End Sub


You can use the match function to find the position in a 1 dimensional array of an item. This can then be applied to another array.

Problem:
You have mulitple department numbers for a super department. One way to overcome this would be to use a lookup table to convert the dept number into the super dept number before putting into the arrays. You would also have to determine if an entry already existed and if so, then add additional entries.

Food for thought.


Tony

malik641
08-07-2005, 08:43 AM
Tony,
Thanks for the info. I've veered away from the array idea. I got the program as far as to match the department in the second table from the first. All I have to do now is to place the value of "num" into the adjacent column of the row that the match had found. This is where I'm completely stuck. Check out my example...this is SOO close to being finished!!

acw
08-07-2005, 09:16 PM
Joseph

Have a look at the attached file. I've put in an extra sheet called acw. In this I put the output numbers that would result from your process for ease of explanation.

I've created a new function called NewFunc. I also added a lookup range as a named range LU_Range. It sits at H50:I85. Basically, if you only have the range it will perform the sum only (refer C71), but if you have a heading, the it will provide the sum for that heading only. The lookup table is used to convert all the individual codes to the group code (refer C50:C70).

See what you think.

Tony