Macros/VBA to fill time card

[Bob K.]

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

 

[K Dales]

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

 

[Tom Ogilvy]

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

 

[Bob K.]

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