Hello everyone. I have been a member of this site for almost 2 years and have learned a ton! Unfortunately I am venturing into VBA and have not found a solution for what I want.
I have an excel sheet I use to track and employees absence. I will explain what I currently do so you have an idea.
On the Month tab ie JAN, I paste in information from a report, each day I paste on the next empty line the new days absenteeism report and do that each day.
I have created code on the monthly tab that converts the pasted date into something I can index/match and paste the results into the EMER tab which is like a 365 day calendar so we can track Absenteeism and look for patterns.
What I currently have works well, and does exactly what I want, but with 1500 rows and 366 columns , but it is 550K cells filled with code which is slow.
I currently use this code: =IFERROR(INDEX(JAN!$J$1:$J$3000,MATCH(1,(JAN!$G$1:$G$3000=Q$1)*(JAN!$A$1:$A$3000=$B10),0)),"") entered with CTRL+ALT+Enter. into 550K cells in the EMER tab.
The code is adjusted 12 times, once for each month.
The above code is easy to enter as I can drag across and down, but it needs to be different for each month and the Info I paste for each month is on a different tab.
I will explain the code above.
INDEX(JAN!$J$1:$J$3000 - This is the information I want pasted in each cell ie "E", "T", "NC", and others if I add
MATCH(1,(JAN!$G$1:$G$3000=Q$1) - This matches the date in JAN $G$1:$G$3000 with the date in the first row of each column IE: EMER $Q$1
*(JAN!$A$1:$A$3000=$B10) - This match the employee name in JAN A1:A3000 with the name in EMER $B$10:$B$1510
I would like to be able to do this with VBA for many reasons including, size, speed and ease of use and updating requirements as they change.
I have attached an extremely small version of my tracker, and changed the names etc as this is a sensitive document.
I appreciate any and all assistance![]()
Bookmarks