• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Replacement for Huge Arrays

K

kavsmate

New Member
I’m entirely stuck and I don’t know how to progress, so any help will be greatly appreciated. What I want is fairly straightforward, but is fiddly and difficult to explain, so please bear with me. I’m currently achieving what I need using 14,600 insanely long array formulas which, obviously, isn’t ideal as I have to have calculations set to manual, which is screwing up other aspects of the sheet (when run, the calculations take about a minute).


I have workbook with 2 sheets on it called sheet1 & Calendar respectively.


Sheet1 contains a data dump from a database used to store staff holiday information. Columns A through H are used. The columns I’m interested in are A, B, C & D which are headed: Name, Date From, Date to and Type.


There are 4 codes for holiday/leave type: AL, FL, SL and Sick.


The Calendar uses columns A to NB. Column A contains a list of names down to Row 40, Row contains the numbers 1 or 2 (I’ll explain that in a minute), Row 2 contains the dates for the entire year (from column B to column NB).


Cells B3 to NB43 contain massive Sumproduct arrays. These arrays compare the Name in column A and the date in row 2 against columns A, B and C on sheet1 and enter the relevant information from column D.


The numbers in row 1 are slightly different, but are still part of the same array.


On sheet1 columns R to Y are used to display a 2 week grid for each staff member that displays what days they don’t work on. This information also comes from the database.


This would obviously be continued across the entire year for each member of staff.


The formula that currently produces this information is this:


Code: 
=IF(COUNTIF(INDEX(Sheet1!$S:$Y,SMALL(IF(Sheet1!$R$2:$R$100=$A4,ROW(Sheet1!$R$2:$R$100)),B$1),0),B$2), "NW Day",IF(SUMPRODUCT(--(Sheet1!$A$2:$A$3520=$A4), --(Sheet1!$B$2:$B$3520<=B$3),--(Sheet1!$C$2:$C$3520>=B$3),--(Sheet1!$D$2:$D$3520="AL") , --(ISTEXT(Sheet1!$K$2:$K$3520))), "AL" ,IF(SUMPRODUCT(--(Sheet1!$A$2:$A$3520=$A4), --(Sheet1!$B$2:$B$3520<=B$3), --(Sheet1!$C$2:$C$3520>=B$3),--(Sheet1!$D$2:$D$3520="SL") , --(ISTEXT(Sheet1!$K$2:$K$3520))), "SL",IF(SUMPRODUCT(--(Sheet1!$A$2:$A$3520=$A4), --(Sheet1!$B$2:$B$3520<=B$3),--(Sheet1!$C$2:$C$3520>=B$3),--(Sheet1!$D$2:$D$3520="FL") , --(ISTEXT(Sheet1!$K$2:$K$3520))), "FL",IF(SUMPRODUCT(--(Sheet1!$A$2:$A$3520=$A4), --(Sheet1!$B$2:$B$3520<=B$3), --(Sheet1!$C$2:$C$3520>=B$3), --(Sheet1!$D$2:$D$3520="Sick")),"Sick","")))))


Which, as I said, sits in 14,600 cells on the calendar (365 x 40 staff).


Is there any way that this can be achieved with a VBA macro rather than my ridiculously long arrays? If it could, it would mean that I would be able to have calculations set to automatic and other aspects of the workbook would work as they’re supposed to (I know I can set certain sheets to calculate manually, but other coding in the workbook (not mine) has rendered this not practical.


NB: I keep trying to add examples, but I can't get the formatting to hold.
 
Hi, kavsmate!

Besides posting a sample workbook, would you please facilitate things to people who might help and describe in words with examples what do you want to achieve with the formulas in 2nd sheet? I think it might be more helpful and would avoid to misinterpret such formulas. Thank you.

Regards!
 
Thanks guys.


The example workbook can be found here:


https://docs.google.com/file/d/0Bw84rcZ7LkG6ZUlldGI5X0FYREU/edit?usp=sharing


It contains a small version of the actual document with sample information on it. The information (dates, codes) on sheet1 can be adjusted and it will change on the calendar sheet). This should show you what the formulas do, and the outcome I'm looking to achieve with VBA.


Adjusting the dates on sheet1 for Dave or Steve (the only names available for the example) will make the code in column D appear under the correct dates on the calendar. Adjusting the code in column D (AL, FL, SL, Sick) will make that code appear under the relevant dates on the calendar sheet.


Columns R also contains staff names (2 rows per staff member), with columns S-Y being spaces for the days of the week enter as 3 characters (column S can contain Sun (for sunday), T can contain Mon (for Monday), column U can contain Tue (for Tuesday) etc). The first row for each staff member is week 1 and the second row for each staff member is week 2. From the example information you should be able to see that if a member of staff doesn't work a day during week 1, the code NW is entered on every week 1 day (identified with a 1 above the date on the calendar) for the year.


What I would like is for all of this information to be entered with VBA at the click of a button rather than with the thousands of formulas I have. This would enable me to have the workbook on automatic calculations all the time and will speed things up in other areas no end. Besides, sumproduct is great, but only in small numbers.


The cells I need this information in is the range B4:NB43.
 
Hi Kavsmate


I will try and take a look at it today. From a quick glance it looks doable.


You should also take a look at the below which might be useful for you:


http://chandoo.org/wp/2013/01/24/employee-vacations-tracker-dashboard/
 
You must log in or register to reply here.
Back
Top