So I have a project I am working on that is quite extensive.
Basically I have an attendance sheet that is a giant flat file. This is fixed and cannot be changed. It has room for 350 employees (350 rows) and 44 columns worth of data for each employee, which represents Monday, this is then repeated 6 more times to fill out the week (308 Columns plus some totals). This is again multiplied by 13 weeks (1 quarter), each week on its own sheet. So BIG workbook.
I need to create database entries so that I can setup a mail merge data file that can then be run to create reports to hand out to each employee with all “occurrences” of Absent – Late – Leave Early – and Tardy Absent (coming in so late or leaving so early they were out more than half day).
If I can figure out how to do Monday I can set it up in a loop to do the rest of the days of the week and then set that up in a loop to do the weekly sheets.
Mondays Columns have an “x” that marks if there is an absent in column AG, Tardy Absent in AH, Tardy In in AI and Tardy Out in AJ. An employee could have multiple “occurrences” so I have to iterate each column separately.
<tbody>
[TD="align: center"][/TD]
[TD="align: center"]AG[/TD]
[TD="align: center"]AH[/TD]
[TD="align: center"]AI[/TD]
[TD="align: center"]AJ[/TD]
[TD="align: center"]09[/TD]
[TD="align: center"]Absent[/TD]
[TD="align: center"]Tardy Absent[/TD]
[TD="align: center"]Tardy In[/TD]
[TD="align: center"]Tardy Out[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]x[/TD]
</tbody>
I am self-taught, but have been doing this a while. So I have some skills I just have trouble deciding the best way to do it.
This is basically how I would set it up based on my current skill level:
I would use VBA to set up a table out to the right of the flat file area which I would populate with formulas similar to:
Once I had that set up for Absent Occurrences I would
Then I would repeat the process for Tardy Absent, Tardy In and then Tardy Out.
Then I would repeat those processes for the rest of the 6 days.
Then I would repeat that whole deal for each of the 13 sheets in the workbook.
As you can see this is a big job and would take a long time to process the way I am planning.
I am looking for a better way to "programatically" process this, possibly skipping some/many of the steps above.
I need a VBA super formula that will grab the data based on looping through each of the Occurrence columns and paste a series of cells based on the row I’m in and set it up somewhere else. I really just need the part in Bold below to get me going:
Thanks in advance for any direction you can give me.
Basically I have an attendance sheet that is a giant flat file. This is fixed and cannot be changed. It has room for 350 employees (350 rows) and 44 columns worth of data for each employee, which represents Monday, this is then repeated 6 more times to fill out the week (308 Columns plus some totals). This is again multiplied by 13 weeks (1 quarter), each week on its own sheet. So BIG workbook.
I need to create database entries so that I can setup a mail merge data file that can then be run to create reports to hand out to each employee with all “occurrences” of Absent – Late – Leave Early – and Tardy Absent (coming in so late or leaving so early they were out more than half day).
If I can figure out how to do Monday I can set it up in a loop to do the rest of the days of the week and then set that up in a loop to do the weekly sheets.
Mondays Columns have an “x” that marks if there is an absent in column AG, Tardy Absent in AH, Tardy In in AI and Tardy Out in AJ. An employee could have multiple “occurrences” so I have to iterate each column separately.
<tbody>
[TD="align: center"][/TD]
[TD="align: center"]AG[/TD]
[TD="align: center"]AH[/TD]
[TD="align: center"]AI[/TD]
[TD="align: center"]AJ[/TD]
[TD="align: center"]09[/TD]
[TD="align: center"]Absent[/TD]
[TD="align: center"]Tardy Absent[/TD]
[TD="align: center"]Tardy In[/TD]
[TD="align: center"]Tardy Out[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]x[/TD]
</tbody>
I am self-taught, but have been doing this a while. So I have some skills I just have trouble deciding the best way to do it.
This is basically how I would set it up based on my current skill level:
I would use VBA to set up a table out to the right of the flat file area which I would populate with formulas similar to:
[*=1]=If(AG10=”x”,”Absent”,””) / Get Type of Occurrence
[*=1]=If(AG10=”x”,Q10,””) / Get Employee Name
[*=1]=If(AG10=”x”,$BE$3,””) / Get Date
[*=1]=If(AG10=”x”,AS10,””) / Get Counseling Points
[*=1]=If(AG10=”x”,BF10,””) / Get Cumulative Counseling Points
[*=1]=If(AG10=”x”,BG10,””) / Get Warning Level
[*=1]Etc… etc…
Once I had that set up for Absent Occurrences I would
- Fill the formulas down 350 rows
- Copy the table
- Paste the table as values to remove the formulas
- Sort it to remove any blanks
- Copy the remaining data again
- Paste it to a new Workbook below any existing data
Then I would repeat the process for Tardy Absent, Tardy In and then Tardy Out.
Then I would repeat those processes for the rest of the 6 days.
Then I would repeat that whole deal for each of the 13 sheets in the workbook.
As you can see this is a big job and would take a long time to process the way I am planning.
I am looking for a better way to "programatically" process this, possibly skipping some/many of the steps above.
I need a VBA super formula that will grab the data based on looping through each of the Occurrence columns and paste a series of cells based on the row I’m in and set it up somewhere else. I really just need the part in Bold below to get me going:
Code:
Open New workbook
Setup labels on A1 - J1
Switch back to original workbook
Loop 13 times, once for each Sheet in Workbook[INDENT]Loop 7 times within each sheet for weekdays[/INDENT]
[INDENT][I]offset 44 columns each loop through[/I][/INDENT]
[INDENT=2]Loop 4 times for occurrences[/INDENT]
[INDENT=3][B]Loop 350 times through data from list, based on the “x” in absent section[/B][/INDENT]
[INDENT=4][B][I]wb2.ws2.cells to put data in = wb1.ws1.cells to get data from[/I][/B][/INDENT]
[INDENT=3][B]Next Row[/B][/INDENT]
[INDENT=2]Next Occurrence Loop[/INDENT]
[INDENT]Next Weekday Loop[/INDENT]
Next Week LoopThanks in advance for any direction you can give me.
