Moving Firemen around

[markstoehr]

I have a daily staffing sheet that I'm trying to move firemen from one truck to another. It shows how many firemen are on a truck on a given day. Currently, I have to manually type the name of the Firefighter when moving. I want to try to make this automatic.

The sheet is divided into Trucks and in each truck, there are 4 columns representing 6-hour shifts in the 24hours that we work. On each truck, there are the permanent Firemen. But sometimes if someone is sick for example Firemen need to move to another truck.

I want to put the number of the hall beside the fireman's name (shown in red) and have them automatically show up on the indicated truck in the gray area (shown in blue). This would require some VBA. Firemen can be moved to any truck. I would have a drop menu beside each name so as not to have an error on entry. S = Sick

An example of a move would be D Rowntree that works on Truck R426 needs to move to Truck R423 for the 4 time periods. I enter R423 beside his name and he is automatically added in the gray area under R423 and then added to the total on the truck.

Any help on the VBA part would greatly be appreciated. I normally do coding in PHP and am lagging behind here.

Thanks in advance

​

 

[My Aswer Is This]

I would have these questions:

Is the five we see in "A1" "K1" "A13" and "K13"

And what cells are the Truck numbers in? It's hard from this image to see that.

Is it "B1" "L1" "B13" and "L13"

And you said:
"I want to put the number of the hall beside the fireman's name (shown in red)

Do you mean number of the "Truck"

 

[Peter_SSs]

Welcome to the MrExcel board!

Some further comments/questions

1. It would be very helpful if you could provide the sample data in a form helpers could Copy?Paste as most are all that keen on manually typing it out.
See my signature block below for further help about that.

2. A word of warning. It appears that you may have amny merged cells. merged cells and VBA don't always go very well together. As an example, you appear to have the 'name' cells in columns D & E merged. If they are merged, could you consider unmerging them and simply making column D wider and either delete or hide column E?

3. You have shown 2 sets of truck column and 2 sets of truck rows. How many do you actually have? Just trying to get an idea of the scale of the data.

4. In all your examples where you have filled in red data beside a name, you have filled all 4 cells the same. Am I correct in thinking that would not always be the case?

 

[markstoehr]

Thank you for the responses.

I have redone the sheet containing NO merged cells. Here is a link to download it. https://www.dropbox.com/s/4nht6hnhhqfadnt/staff.xlsx?dl=0

I will try to explain the sheet. I will use R423 as the example all trucks should be the same.

A1 is the maximum staff allowed on the truck.
B1 is the minimum staff allowed on the truck.
D1 is the truck name
A2:A7 will contain staff employee numbers (removed for this example)
B2:B7 is their rank
C2:C7 first initial
D2:D7 last name
E:E I meant to remove this column (oops)
F1:I1 is a count of staff on the truck (conditional formatting for color)
- I will add up the column to get a total staff working
- Each column represents 6Hrs of the 24H work day
- There are 4 columns F:I because staff can move 4 times a day. So it is possible that one or more columns are filled.
- Red is understaffed
- Green is overstaffed
- No color is at minimum staff
F2:I7 This is the only area I want to enter anything
- I would use drop lists so that the input is not entered incorrectly (all trucks and missing codes)
- I want to enter the truck that staff needs to move to or a reason they are not there (S=Sick, V=Vacation, R423=staff moved to R423, etc)
- If they are working on that truck that day enter nothing (they are not off or did not move)
Row 7 is there as it is possible there is another permanent staff
A8:I12 this is where staff that have been moved should show up
- This is where I'm looking for help
- I don't want to have to do any manual entry in this area
- I need some VBA magic to do this for me
- It is not likely that there are more than 5 staff movements so I think the gray area is big enough

Again Thanks in advance

 

[markstoehr]

Another error I found Q423 the numbers 3333 should not have a green background they should have been just white. I did not do any of the calculations or conditional formatting for this example

thanks

 

[Peter_SSs]

Excellent detailed explanation, thanks.

There are still 2 things I am unsure of but I think we can proceed anyway.
a) The Conditional Formatting. Was it right that truck P426 is green even though the number of staff does not exceed the maximum for the truck? The answer doesn't really matter for now as I have not addressed the CF at this stage because I gather that you can do that yourself anyway. If not, we'll deal with it later.
b) You didn't answer my Q2 but what I am suggesting below can be scaled up if required.

My suggestion (Test in a copy of your workbook):
1. We will need the Employee number as that is the easy unique identifier for each person. So I have entered some dummy values.
2. Have a sheet where all the trucks are listed vertically as shown in the first screen shot below. This will be your data entry sheet. Every truck block must be the same size and if you alter that from 12 rows, the code will need adjusting.
3. Leave column E in there since your descriptions were given with that in place. Just hide the column as I have done below.
4. Formula in F1 is copied across to I1 and then those 4 formulas copied to the other heading rows (13, 25, etc)
5. Right click the sheet name tab and choose "View Code".
6. Copy and Paste the code below into the main right hand pane that opens at step 5.
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  Dim HdrRow As Long
  Dim TruckName As String
'Exit Sub
  Set Changed = Intersect(Target, Columns("F:I"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If Len(c.Value) > 0 Then
        HdrRow = Int(c.Row / 12) * 12 + 1
        TruckName = Cells(HdrRow, "D").Value
        MoveOne TruckName, c.Value, c.Row, c.Column
      End If
    Next c
  End If
  Application.EnableEvents = True
End Sub

8. While in the vba window use the menu to Insert -> Module
9. Copy and Paste the code below into the main right hand pane that opens at step 8.
10. Close the Visual Basic window.

I will continue in another post as this on is getting a bit long

Excel Workbook
	A	B	C	D	F	G	H	I
1	5	4		R423	5	5	5	5
2	E1	ADC	M	Cannon
3	E2	AC	C	Zanussi
4	E3	FF	G	Vandeheuvel
5	E4	FF	M	Seguin
6	E5	FF	P	Doherty
7
8
9
10
11
12
13	5	3		Q423	5	5	5	5
14	E5	FF	P	Doherty
15	E7	AC	D.	Bradshaw
16	E8	FF	J.	Pennock
17	E9	FF	C.	Koinis
18	E10	FF	E	Dawson
19
20
21
22
23
24
25	5	4		R426	5	5	5	5
26	E11	CAPT	G	Faulker
27	E12	AC	J	Vanusio
28	E13	FF	N.	Macelli
29	E14	FF	V.	Wajda
30	E15	FF	D	Rowntree
31
32
33
34
35
36
37	5	4		P426	5	5	5	5
38	E16	CAPT	J.	Fievez
39	E17	AC	J.	Bredin
40	E18	FF	S.	Regier
41	E19	FF	M.	Riley
42	E20	FF	P.	Drozd
43
44
45
46
47
48
Sheet6 (1)

 

[Peter_SSs]

Try entering your values in columns F:I. this is my sheet after entering the red values you had in your sample.

Excel Workbook
	A	B	C	D	F	G	H	I
1	5	4		R423	3	3	3	3
2	E1	ADC	M	Cannon	S	S	S	S
3	E2	AC	C	Zanussi
4	E3	FF	G	Vandeheuvel
5	E4	FF	M	Seguin
6	E5	FF	P	Doherty	R426	R426	R426	R426
7
8
9
10
11
12
13	5	3		Q423	3	3	3	3
14	E5	FF	P	Doherty
15	E7	AC	D.	Bradshaw
16	E8	FF	J.	Pennock	S	S	S	S
17	E9	FF	C.	Koinis	P426	P426	P426	P426
18	E10	FF	E	Dawson	R426	R426	R426	R426
19
20	E15	FF	D	Rowntree	R426	R426	R426	R426
21
22
23
24
25	5	4		R426	4	4	4	4
26	E11	CAPT	G	Faulker
27	E12	AC	J	Vanusio	S	S	S	S
28	E13	FF	N.	Macelli	S	S	S	S
29	E14	FF	V.	Wajda
30	E15	FF	D	Rowntree	Q423	Q423	Q423	Q423
31
32	E10	FF	E	Dawson	Q423	Q423	Q423	Q423
33	E5	FF	P	Doherty	R423	R423	R423	R423
34
35
36
37	5	4		P426	5	5	5	5
38	E16	CAPT	J.	Fievez
39	E17	AC	J.	Bredin
40	E18	FF	S.	Regier
41	E19	FF	M.	Riley
42	E20	FF	P.	Drozd	S	S	S	S
43
44	E9	FF	C.	Koinis	Q423	Q423	Q423	Q423
45
46
47
48
Sheet6 (2)

Further notes:
a) The code will need more I'm sure. For one thing if you make a mistake and want to change or delete one of the red values, at the moment the code will not remove any blue values that have been inserted.
b) If you want to work in the sheet without this code running all the time, you can uncomment (remove the apostrophe from) the 'Exit Sub' line in the first code.
c) If you actually want a sheet with the layout of your original sample, then I suggest that you make a fresh sheet as shown below. The formatting (including Conditional) in this sheet will need to be set up again.

Formula in A1 is copied across to I and down. Similar for the K1 formula (note column O also hidden)

See if that is a starting point.
I would still like to know (about) how many trucks altogether.

Excel Workbook
	A	B	C	D	F	G	H	I	J	K	L	M	N	P	Q	R	S
1	5	4		R423	3	3	3	3		5	4		R426	4	4	4	4
2	E1	ADC	M	Cannon	S	S	S	S		E11	CAPT	G	Faulker
3	E2	AC	C	Zanussi						E12	AC	J	Vanusio	S	S	S	S
4	E3	FF	G	Vandeheuvel						E13	FF	N.	Macelli	S	S	S	S
5	E4	FF	M	Seguin						E14	FF	V.	Wajda
6	E5	FF	P	Doherty	R426	R426	R426	R426		E15	FF	D	Rowntree	Q423	Q423	Q423	Q423
7
8										E10	FF	E	Dawson	Q423	Q423	Q423	Q423
9										E5	FF	P	Doherty	R423	R423	R423	R423
10
11
12
13	5	3		Q423	3	3	3	3		5	4		P426	5	5	5	5
14	E5	FF	P	Doherty						E16	CAPT	J.	Fievez
15	E7	AC	D.	Bradshaw						E17	AC	J.	Bredin
16	E8	FF	J.	Pennock	S	S	S	S		E18	FF	S.	Regier
17	E9	FF	C.	Koinis	P426	P426	P426	P426		E19	FF	M.	Riley
18	E10	FF	E	Dawson	R426	R426	R426	R426		E20	FF	P.	Drozd	S	S	S	S
19
20	E15	FF	D	Rowntree	R426	R426	R426	R426		E9	FF	C.	Koinis	Q423	Q423	Q423	Q423
21
22
23
24
Sheet6 (3)

 

[markstoehr]

WOW, Peter thanks for the work. l work with this and get back to you

THANKS

 

[markstoehr]

Hello Peter,

I don't quite understand this step
9. Copy and Paste the code below into the main right hand pane that opens at step 8.

You said below but I don't see code below. Maybe I missed it.

I would need the code to remove the blue added lines if errors or changes occur. (The chief don't tell him I said this is not much of a computer person and messes this form up every day) So I will work on this once I get your first example working.

I will need to format to 2 columns as this need to be printed out and chief carries a copy all day. One page is what I'm aiming for.

As for trucks, this is for one area of the department and there are 11 trucks in total. But there are 2 of the trucks that don't fit the 12 rows model

Here are the standard trucks and odd ones are bold and underlined. I have grouped them into the stations they work at.

Station 421
R421 Q421 AL421
Station 422
P422
Station 423
R423 Q423 C42
(there is no station 424)
Station 425
R425
Station 426
P426 R426 Q426

AL421 is a special support truck that only has 2 staff max but 3 permanently assigned to the truck. So there is always one staff off or on another truck. Currently, there is a vacancy.

3 2	AL421	1	1	1	1
1111111	Capt	W	Vit	S	S	S	S
2222222	AC	B	Worth
0000000	FF	V	Vacancy	VAC	VAC	VAC	VAC

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

C42 is the chief car and also only has 2 working on it but only has 2 people permanently assigned to it.

2 2	C42	1	1	1	1
1111111	DC	W	smith	S	S	S	S
3333333	FF	B	Test

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Peter_SSs]

I don't quite understand this step
9. Copy and Paste the code below into the main right hand pane that opens at step 8.

You said below but I don't see code below. Maybe I missed it.

No, I ended up in a bit of a hurry and with the fairly long posts, I forgot to add the second code. Sorry about that. Remember that it goes in a different place to the first code so follow from step 8 again. If you had already done step 8, now you can locate that 'Module1' that step 8 created in the left hand pane of the vba window and double-click it. That should reveal the blank pane on the right to paste the code into. Here's the code.

Sub MoveOne(FromTruck As String, ToTruck As String, OldRow As Long, ChangeCol As Long)
  Dim ToTruckHeaderRow As Long, NewRow As Long
  
  ToTruckHeaderRow = 0
  On Error Resume Next
  ToTruckHeaderRow = Range("D1:D1000").Find(What:=ToTruck, LookIn:=xlValues, LookAt:=xlWhole).Row
  On Error GoTo 0
  If ToTruckHeaderRow > 0 Then
    NewRow = 0
    On Error Resume Next
    NewRow = Cells(ToTruckHeaderRow, "A").Resize(12).Find(What:=Cells(OldRow, "A").Value, LookIn:=xlValues, LookAt:=xlWhole).Row
    On Error GoTo 0
    If NewRow = 0 Then
      NewRow = Cells(ToTruckHeaderRow + 12, "A").End(xlUp).Row + 1
      If NewRow < ToTruckHeaderRow + 7 Then NewRow = ToTruckHeaderRow + 7
    End If
    Cells(NewRow, "A").Resize(, 4).Value = Cells(OldRow, "A").Resize(, 4).Value
    Cells(NewRow, ChangeCol).Value = FromTruck
  End If
End Sub

I would need the code to remove the blue added lines if errors or changes occur.

I thought that would be the case and I think to achieve that I might take a significantly different approach with the code. I possibly may not get to it for a day or so though.

I will need to format to 2 columns as this need to be printed out and chief carries a copy all day. One page is what I'm aiming for.

I think the 2 'different' trucks can still fit into the 12 row model, they will just have more blank rows (and we may be able to hide some of those rows).


Currently our test model has a 2x2 grid of trucks. How do you envisage fitting the 11 trucks onto the sheet for printing? Would that be, say, 6 trucks across and 2 down, or vice-versa, or 4 across and 3 down, or what? (all with one blank section of course)