Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros/VBA to fill time card
I am very new to Visual Basic and hope someone can help me with this.
This is what I am trying to do. The employee time cards at my office are complicated and time-consuming to fill out manually. Each day has its own cells for clock hours worked, total hours, and appropriate pay codes. Nightshift, dayshift, holidays, etc. all require different pay codes. H14 Monday Hours worked total code activity 0600 1400 8 01 supv I created button-activated macros (relative reference) to fill cells for each day and it works well. Each shift has its own button, and can fill any day of the week However, I want the program to do this. If the above hours were worked on Sunday (cell H8) rather than Monday (cell H14) I want the macro to place the code "11" rather than "01" in the H8 cell, without creating a separate macro. H8 Sunday Hours worked total code activity 0600 1400 8 11 supv I work shift work in law enforcement so I have a separate shift buttons (24 in total) for all possible shift start times; I can't create separate buttons for Sunday codes as there would be too many. I don't know if this might require an if/then statement or something else. Or maybe I've gone about this all wrong from the get-go. Thanks for your help. Bob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros/VBA to fill time card
Bob: What you want to do is possible, but we would need to see your code to
know exactly how to implement it. However, you are correct that you will need a conditional (IF) statement. You must have some line in the code that figures out the location of the hours worked (computes the range address) so you then need to check if it is Sunday (row 8?) and use that to figure out the code to go in the destination cell; something like this: If Range(computed).Row = 8 Then Code = "11" Else Code = "01" Range(destination) = Code Wish I could be more specific, but hoepfully this gives you a start. If you need more help, post your code and I (or someone!) will take a look... K Dales "Bob K." wrote: I am very new to Visual Basic and hope someone can help me with this. This is what I am trying to do. The employee time cards at my office are complicated and time-consuming to fill out manually. Each day has its own cells for clock hours worked, total hours, and appropriate pay codes. Nightshift, dayshift, holidays, etc. all require different pay codes. H14 Monday Hours worked total code activity 0600 1400 8 01 supv I created button-activated macros (relative reference) to fill cells for each day and it works well. Each shift has its own button, and can fill any day of the week However, I want the program to do this. If the above hours were worked on Sunday (cell H8) rather than Monday (cell H14) I want the macro to place the code "11" rather than "01" in the H8 cell, without creating a separate macro. H8 Sunday Hours worked total code activity 0600 1400 8 11 supv I work shift work in law enforcement so I have a separate shift buttons (24 in total) for all possible shift start times; I can't create separate buttons for Sunday codes as there would be too many. I don't know if this might require an if/then statement or something else. Or maybe I've gone about this all wrong from the get-go. Thanks for your help. Bob |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros/VBA to fill time card
I assume your macros work relative to the active cell when the button is
pressed - so you could do something like this: Select Case ActiveCell.Row Case 8 Range("G8").Vaue = "'01" Case 14 Range("G14").Value = "'11" End Select -- Regards, Tom Ogilvy "Bob K." wrote in message om... I am very new to Visual Basic and hope someone can help me with this. This is what I am trying to do. The employee time cards at my office are complicated and time-consuming to fill out manually. Each day has its own cells for clock hours worked, total hours, and appropriate pay codes. Nightshift, dayshift, holidays, etc. all require different pay codes. H14 Monday Hours worked total code activity 0600 1400 8 01 supv I created button-activated macros (relative reference) to fill cells for each day and it works well. Each shift has its own button, and can fill any day of the week However, I want the program to do this. If the above hours were worked on Sunday (cell H8) rather than Monday (cell H14) I want the macro to place the code "11" rather than "01" in the H8 cell, without creating a separate macro. H8 Sunday Hours worked total code activity 0600 1400 8 11 supv I work shift work in law enforcement so I have a separate shift buttons (24 in total) for all possible shift start times; I can't create separate buttons for Sunday codes as there would be too many. I don't know if this might require an if/then statement or something else. Or maybe I've gone about this all wrong from the get-go. Thanks for your help. Bob |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros/VBA to fill time card
Thanks for your patience and this is the recorded code. Employees
select the appropriate cell (the first cell in that day of the week,) then click the button for the shift that they want. Sundays are in rows 8-12 while the rest of the days of the week are in rows 14-48. I haven't named these ranges but I can hopefully figure that out, if I need to do that for code purposes. If there is further info I can provide that would help, please let me know. Thanks so much for your help. I could also email the 450 KB file if someone wanted to take a look at it. Bob ActiveCell.FormulaR1C1 = "2200" ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "2400" ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "2" ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "11" ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "SUPV" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "SUPV" ActiveCell.Offset(0, -1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, -1).Range("A1").Select ActiveCell.FormulaR1C1 = "11" ActiveCell.Offset(0, -1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, -1).Range("A1").Select ActiveCell.FormulaR1C1 = "6" ActiveCell.Offset(0, -1).Range("A1").Select ActiveCell.FormulaR1C1 = "0600" ActiveCell.Offset(0, -1).Range("A1").Select ActiveCell.FormulaR1C1 = "0000" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "0600" ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "0800" ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "2" ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "41" ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "SUPV" ActiveCell.Offset(1, 0).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, -1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, -1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, -1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, -1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, -1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, -1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(1, 0).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(-4, -6).Range("A1").Select End Sub (Bob K.) wrote in message . com... I am very new to Visual Basic and hope someone can help me with this. This is what I am trying to do. The employee time cards at my office are complicated and time-consuming to fill out manually. Each day has its own cells for clock hours worked, total hours, and appropriate pay codes. Nightshift, dayshift, holidays, etc. all require different pay codes. H14 Monday Hours worked total code activity 0600 1400 8 01 supv I created button-activated macros (relative reference) to fill cells for each day and it works well. Each shift has its own button, and can fill any day of the week However, I want the program to do this. If the above hours were worked on Sunday (cell H8) rather than Monday (cell H14) I want the macro to place the code "11" rather than "01" in the H8 cell, without creating a separate macro. H8 Sunday Hours worked total code activity 0600 1400 8 11 supv I work shift work in law enforcement so I have separate shift buttons (24 in total) for all possible shift start times; I can't create separate buttons for Sunday codes as there would be too many. I don't know if this might require an if/then statement or something else. Or maybe I've gone about this all wrong from the get-go. Thanks for your help. Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time Card Help | Excel Discussion (Misc queries) | |||
New Time Card | Excel Discussion (Misc queries) | |||
Time Card | Excel Discussion (Misc queries) | |||
Time Card | Excel Worksheet Functions | |||
HOW CAN I FILL AN EMPLOYEE CARD WITH EXACT DAYS CALENDAR | Excel Worksheet Functions |