Hello everyone!
I'm here to humbly ask for your help on VBA ... considering I'm still sooo impractical about VBA .....
I'm trying to create a staff shift scheduler.
I thought it was a simple project .... but I guess I ran aground ....
As per attached file, I hypothesized a H24 7 days a week system. which are repeated month by month (clearly dynamically and not statically)
The system repeats the following way
1= PP RR NN RR M M
2= RR MM PP RR N N
3= NN RR MM PP RR
4= MM PP RR N N RR
5= RR NN RR MM PP
where P= Pomeridian
R= Rest
N= Night
M= Mornings
so, 1= 2 days of pomeridian, 2 days rest, 2 nights, 2 rest, 2 mornings....rinse and repeat lol
The part of the code, on the other hand, which should write the shifts on all the lines does not work ... I select the shift of group 1 it writes the relative shift only on the first line ... I started writing the code where I automatically enter dates / gg, and so far everything is fine. I did not understand how to write...
The best solution would be for the code to read the last two days of the previous month and write the new one....but not there yet as knowledge necessary XD
any help?
attached link to sample file
Sample File
I'm here to humbly ask for your help on VBA ... considering I'm still sooo impractical about VBA .....
I'm trying to create a staff shift scheduler.
I thought it was a simple project .... but I guess I ran aground ....
As per attached file, I hypothesized a H24 7 days a week system. which are repeated month by month (clearly dynamically and not statically)
The system repeats the following way
1= PP RR NN RR M M
2= RR MM PP RR N N
3= NN RR MM PP RR
4= MM PP RR N N RR
5= RR NN RR MM PP
where P= Pomeridian
R= Rest
N= Night
M= Mornings
so, 1= 2 days of pomeridian, 2 days rest, 2 nights, 2 rest, 2 mornings....rinse and repeat lol
The part of the code, on the other hand, which should write the shifts on all the lines does not work ... I select the shift of group 1 it writes the relative shift only on the first line ... I started writing the code where I automatically enter dates / gg, and so far everything is fine. I did not understand how to write...
VBA Code:
Sub ins1()
Dim ID As Integer
Range("D6").Select
Do While ActiveCell.Offset(-1, 0).Range("a1").Value <> ""
ID = ActiveCell.Offset(-1, 0).Range("a1") - 37883
Select Case Right(ID, 1)
Case 0 To 1
ActiveCell.Value = "P"
Case 2 To 3
ActiveCell.Value = ""
Case 4 To 5
ActiveCell.Value = "N"
Case 6 To 7
ActiveCell.Value = ""
Case 8 To 9
ActiveCell.Value = "M"
Case Else
MsgBox "Error...You Should never see this"
End Select
ActiveCell.Offset(0, 1).Range("a1").Select
Loop
Range("D7").Select
Do While ActiveCell.Offset(-1, 0).Range("a1").Value <> ""
Select Case Right(ID, 1)
Case 0 To 1
ActiveCell.Value = "P"
Case 2 To 3
ActiveCell.Value = ""
Case 4 To 5
ActiveCell.Value = "N"
Case 6 To 7
ActiveCell.Value = ""
Case 8 To 9
ActiveCell.Value = "M"
Case Else
MsgBox "Error...You Should never see this"
End Select
ActiveCell.Offset(0, 1).Range("a1").Select
Loop
Range("D8").Select
Do While ActiveCell.Offset(-1, 0).Range("a1").Value <> ""
Select Case Right(ID, 1)
Case 0 To 1
ActiveCell.Value = ""
Case 2 To 3
ActiveCell.Value = "M"
Case 4 To 5
ActiveCell.Value = "P"
Case 6 To 7
ActiveCell.Value = "N"
Case 8 To 9
ActiveCell.Value = ""
Case Else
MsgBox "Error...You Should never see this"
End Select
ActiveCell.Offset(0, 1).Range("a1").Select
Loop
Range("D9").Select
Do While ActiveCell.Offset(-1, 0).Range("a1").Value <> ""
Select Case Right(ID, 1)
Case 0 To 1
ActiveCell.Value = ""
Case 2 To 3
ActiveCell.Value = "M"
Case 4 To 5
ActiveCell.Value = "P"
Case 6 To 7
ActiveCell.Value = "N"
Case 8 To 9
ActiveCell.Value = ""
Case Else
MsgBox "Error...You Should never see this"
End Select
ActiveCell.Offset(0, 1).Range("a1").Select
Loop
Range("D10").Select
Do While ActiveCell.Offset(-1, 0).Range("a1").Value <> ""
Select Case Right(ID, 1)
Case 0 To 1
ActiveCell.Value = "N"
Case 2 To 3
ActiveCell.Value = ""
Case 4 To 5
ActiveCell.Value = "M"
Case 6 To 7
ActiveCell.Value = "P"
Case 8 To 9
ActiveCell.Value = ""
Case Else
MsgBox "Error...You Should never see this"
End Select
ActiveCell.Offset(0, 1).Range("a1").Select
Loop
Range("D11").Select
Do While ActiveCell.Offset(-1, 0).Range("a1").Value <> ""
Select Case Right(ID, 1)
Case 0 To 1
ActiveCell.Value = "N"
Case 2 To 3
ActiveCell.Value = ""
Case 4 To 5
ActiveCell.Value = "M"
Case 6 To 7
ActiveCell.Value = "P"
Case 8 To 9
ActiveCell.Value = ""
Case Else
MsgBox "Error...You Should never see this"
End Select
ActiveCell.Offset(0, 1).Range("a1").Select
Loop
Range("D12").Select
Do While ActiveCell.Offset(-1, 0).Range("a1").Value <> ""
Select Case Right(ID, 1)
Case 0 To 1
ActiveCell.Value = "M"
Case 2 To 3
ActiveCell.Value = "P"
Case 4 To 5
ActiveCell.Value = ""
Case 6 To 7
ActiveCell.Value = "N"
Case 8 To 9
ActiveCell.Value = ""
Case Else
MsgBox "Error...You Should never see this"
End Select
ActiveCell.Offset(0, 1).Range("a1").Select
Loop
Range("D13").Select
Do While ActiveCell.Offset(-1, 0).Range("a1").Value <> ""
Select Case Right(ID, 1)
Case 0 To 1
ActiveCell.Value = "M"
Case 2 To 3
ActiveCell.Value = "P"
Case 4 To 5
ActiveCell.Value = ""
Case 6 To 7
ActiveCell.Value = "N"
Case 8 To 9
ActiveCell.Value = ""
Case Else
MsgBox "Error...You Should never see this"
End Select
ActiveCell.Offset(0, 1).Range("a1").Select
Loop
Range("D14").Select
Do While ActiveCell.Offset(-1, 0).Range("a1").Value <> ""
Select Case Right(ID, 1)
Case 0 To 1
ActiveCell.Value = ""
Case 2 To 3
ActiveCell.Value = "N"
Case 4 To 5
ActiveCell.Value = ""
Case 6 To 7
ActiveCell.Value = "M"
Case 8 To 9
ActiveCell.Value = "P"
Case Else
MsgBox "Error...You Should never see this"
End Select
ActiveCell.Offset(0, 1).Range("a1").Select
Loop
Range("D15").Select
Do While ActiveCell.Offset(-1, 0).Range("a1").Value <> ""
Select Case Right(ID, 1)
Case 0 To 1
ActiveCell.Value = ""
Case 2 To 3
ActiveCell.Value = "N"
Case 4 To 5
ActiveCell.Value = ""
Case 6 To 7
ActiveCell.Value = "M"
Case 8 To 9
ActiveCell.Value = "P"
Case Else
MsgBox "Error...You Should never see this"
End Select
ActiveCell.Offset(0, 1).Range("a1").Select
Loop
End Sub
The best solution would be for the code to read the last two days of the previous month and write the new one....but not there yet as knowledge necessary XD
any help?
attached link to sample file
Sample File