I have a question about VBA autofill. Below is my table of shift pattern, it calculates the number of people per shift On Premise vs Scheduled.
The code Im using in VBA:
As you can see, cells F to H autofills to row 8 but that row should not be autofilled. It is also like this for all the other sections where autofill ends at wherever column A ends with data, which is not what I want.
The autofill for the other section is pretty much the same, but adjusting the LastRow#
YUL2 Site HR tool v2.2.xlsb | ||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | |||
1 | INBOUND | ICQA | OUTBOUND | |||||||||||||||||||||||||
2 | Day | On Premise | Scheduled | % on 7/29 | Night | On Premise | Scheduled | % on 7/29 | Day | On Premise | Scheduled | % on 7/29 | Night | On Premise | Scheduled | % on 7/29 | Day | On Premise | Scheduled | % on 7/29 | Night | On Premise | Scheduled | % on 7/29 | ||||
3 | DA5C0700 | 4 | 5 | 0.8 | NA5C1800 | 0 | 5 | 0 | DA5C0700 | 5 | 5 | 1 | NA5C1800 | 5 | 5 | 1 | DA5C0730 | 16 | 18 | 0.88888889 | NA5C1830 | 16 | 18 | 0.88888889 | ||||
4 | DA6C0700 | 3 | 4 | 0.75 | NA6C1800 | 1 | 4 | 0.25 | DB3C0700 | 5 | 5 | 1 | NB3C1800 | 5 | 5 | 1 | DB3C0730 | 11 | 11 | 1 | NB3C1830 | 11 | 11 | 1 | ||||
5 | DB2C0700 | 4 | 4 | 1 | NB2C1800 | 0 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | DC7C0730 | 0 | 8 | 0 | 0 | 8 | 0 | |||||||
6 | DB3C0700 | 5 | 6 | 0.83333333 | NB3C1800 | 0 | 6 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | DN4C0730 | 0 | 11 | 0 | 0 | 11 | 0 | |||||||
7 | DL4C0700 | 0 | 3 | 0 | NL4C1800 | 1 | 3 | 0.33333333 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||||||||
8 | DN4C0700 | 0 | 5 | 0 | 0 | 5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||||||||
Calculations |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D2,Z2,V2,Q2,M2,H2 | D2 | ="% on "&TEXT(Dashboard!$I$3,"m/d") |
B3:B8 | B3 | =COUNTIFS('Shift Punches'!$A:$A,$A$1,'Shift Punches'!$E:$E,$A3) |
G3:G8,C3:C8 | C3 | =COUNTIFS(Roster!$E:$E,$A$1,Roster!$G:$G,$A3) |
Z3:Z8,V3:V8,Q3:Q8,H3:H8,D3:D8 | D3 | =IF(C3,B3/C3,0) |
F3:F8 | F3 | =COUNTIFS('Shift Punches'!$A:$A,$A$1,'Shift Punches'!$E:$E,$E3) |
O3:O8,K3:K8 | K3 | =COUNTIFS('Shift Punches'!$A:$A,$J$1,'Shift Punches'!$E:$E,$J3) |
P3:P8,L3:L8 | L3 | =COUNTIFS(Roster!$E:$E,$J$1,Roster!$G:$G,$J3) |
M3:M8 | M3 | =IF(L3,L3/L3,0) |
X3:X8,T3:T8 | T3 | =COUNTIFS('Shift Punches'!$A:$A,$S$1,'Shift Punches'!$E:$E,$S3) |
Y3:Y8,U3:U8 | U3 | =COUNTIFS(Roster!$E:$E,$S$1,Roster!$G:$G,$S3) |
The code Im using in VBA:
VBA Code:
LastRow1 = ActiveSheet.Cells(Rows.Count, 1).End(3).Row
'IB day
Range("B3").Select
Range("B3").Value2 = "=COUNTIFS('Shift Punches'!$A:$A,$A$1,'Shift Punches'!$E:$E,$A3)"
Range("C3").Select
Range("C3").Value2 = "=COUNTIFS(Roster!$E:$E,$A$1,Roster!$G:$G,$A3)"
Range("D3").Select
Range("D3").Value2 = "=IF(C3,B3/C3,0)"
Range("B3:D3").Select
If LastRow1 > 2 Then
Selection.AutoFill Destination:=Range("B3:D" & LastRow1)
End If
'IB nights
LastRow2 = ActiveSheet.Cells(Rows.Count, 1).End(3).Row
Range("F3").Select
Range("F3").Value2 = "=COUNTIFS('Shift Punches'!$A:$A,$A$1,'Shift Punches'!$E:$E,$E3)"
Range("G3").Select
Range("G3").Value2 = "=COUNTIFS(Roster!$E:$E,$A$1,Roster!$G:$G,$A3)"
Range("H3").Select
Range("H3").Value2 = "=IF(G3,F3/G3,0)"
Range("F3:H3").Select
If LastRow2 > 2 Then
Selection.AutoFill Destination:=Range("F3:H" & LastRow2)
End If
The autofill for the other section is pretty much the same, but adjusting the LastRow#