purple_ninja
11-02-2016, 12:43 AM
I have a file that sits on a network drive that runs VBA to add columns and then paste in formulas / values. The network performance an be intermittent and bandwidth occasionally a problem and I *think* that this might be causing my vba to skip every now and then as there isn't any information to process?
The effect is that sometimes when I run the code (usually down about 7k rows and across 24) it misses rows - not always the same ones and it can process huge chunks (eg. hundreds or thousands of lines) and then skip one or two rows before it starts again which result in all my calculations being out. However, it doesn't happen all the time and only appears to have been happening in the last few weeks for a file that I've been using for the last year so I'm assuming that it's not the file or the code causing the problem.
What could be the causes of this? Are there any fail safes I c an build into the code (below) to check and see if the file is available to process and if not wait?
Sub update_all_data()
Dim lastrow As Long
Dim WS As Worksheet
Dim THB As Worksheet
Dim MTD As Worksheet
Set MTD = Sheets("MODTRACKER DATA")
Set THB = Sheets("Total Hours Booked")
Application.ScreenUpdating = False
'clear all auto filters
MTD.ListObjects(1).AutoFilter.ShowAllData
THB.ListObjects(1).AutoFilter.ShowAllData
'Update Columns AB:AM by inserting formulas and then pasting the result
With MTD.Columns("AD:AS").ClearContents
MTD.Range("AD1").FormulaR1C1 = "Resource Pool Check"
MTD.Range("AE1").FormulaR1C1 = "Month_"
MTD.Range("AF1").FormulaR1C1 = "Week_"
MTD.Range("AG1").FormulaR1C1 = "Tech Dept"
MTD.Range("AH1").FormulaR1C1 = "Resource Type"
MTD.Range("AI1").FormulaR1C1 = "Contract Name"
MTD.Range("AJ1").FormulaR1C1 = "Resource Contract"
MTD.Range("AK1").FormulaR1C1 = "Contract Type"
MTD.Range("AL1").FormulaR1C1 = "Year_"
MTD.Range("AM1").FormulaR1C1 = "Platform"
MTD.Range("AN1").FormulaR1C1 = "Parent Contract"
MTD.Range("AO1").FormulaR1C1 = "Role"
MTD.Range("AP1").FormulaR1C1 = "HoD"
MTD.Range("AQ1").FormulaR1C1 = "Booking Status"
MTD.Range("AR1").FormulaR1C1 = "PREBOOKED TIMELAG"
MTD.Range("AS1").FormulaR1C1 = "TIMELAG"
End With
With MTD.Range("R2")
lastrow = .Offset(0, 2).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=[@SumOfNmhrs]+[@SumOfOThrs]"
.Value = .Value
End With
End With
With MTD.Range("AD2")
lastrow = .Offset(0, -8).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=iferror(INDEX(RNAME,MATCH(USERNAME,MODTRACKERID,0)),""Not Found"")"
.Value = .Value
End With
End With
With MTD.Range("AE2")
lastrow = .Offset(0, -9).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=TEXT((TDATE),""MMMM"")"
.Value = .Value
End With
End With
With MTD.Range("AF2")
lastrow = .Offset(0, -10).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=WEEKNUM(Table_TIMESHEET[@Tdate],21)"
.Value = .Value
End With
End With
With MTD.Range("AG2")
lastrow = .Offset(0, -11).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=iferror(INDEX(TECHDEPT,MATCH(USERNAME,MODTRACKERID,0)),""Not Found"")"
.Value = .Value
End With
End With
With MTD.Range("AH2")
lastrow = .Offset(0, -12).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IF(AND(ISNUMBER(SEARCH(""*Offshore*"",RPCHECK)),ISNUMBER(SEARCH(""*VA/VE*"",PROJECTTYPE))),""VA/VE"", IF(AND(ISNUMBER(SEARCH(""*Offshore*"",RPCHECK)),ISNUMBER(SEARCH(""*VCE*"", PROJECTTYPE))),""Vehicle Concepts"", IF(AND(ISNUMBER(SEARCH(""*Offshore*"",RPCHECK)),ISNUMBER(SEARCH(""*DEMO*"", PROJECTTYPE))),""Demo Vehicles"", IF(AND(ISNUMBER(SEARCH(""*Offshore*"",RPCHECK)),ISNUMBER(SEARCH(""*Electric*"",AE:AE))),INDEX(TECHDEPTALIAS,MATCH(RPCHECK,RPNAME,0)),IF(AND(ISNUMBER(SEAR CH(""*Offshore*"",RPCHECK)),ISNUMBER(SEARCH(""*NPD*"",CTYPE))),CONCATENATE(""NPD - "",INDEX(TECHDEPTALIAS,MATCH(RPCHECK,RPNAME,0))),IF(AND(ISNUMBER(SEARCH(""*Offshore*"",RPCHECK)),ISNUMBER(SEARCH(""*CME"",CTYPE))),CONCATENATE(""CME - "",INDEX(TECHDEPTALIAS,MATCH(RPCHECK,RPNAME,0))), INDEX('Total Hours Booked'!R:R,MATCH(RPCHECK,RPNAME,0))))))))"
.Value = .Value
End With
End With
With MTD.Range("AI2")
lastrow = .Offset(0, -13).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IFERROR(IF(ISBLANK(USERNAME),"""",IF(ISNUMBER(SEARCH(""*Holiday*"",TSREFDES)),""Holiday"",IF(ISNUMBER(SEARCH(""*Sickness*"",TSREFDES)),""Sickness"", IF(ISNUMBER(SEARCH(""*Absence - Other Approved*"",TSREFDES)),""Other Absence"",IF(ISNUMBER(SEARCH(""*RQ*"",F:F)),""RQI"",IF(ISNUMBER(SEARCH(""*NF_*"",Table_TIMESHEET[@ProjectType])),""New Flyer"",IF(ISNUMBER(SEARCH(""*SALES_*"",Table_TIMESHEET[@ProjectType])),""CME"",(INDEX(ALIAS,MATCH(PROJPIV,Planalias,0)))))))))),""Not Found"")"
.Value = .Value
End With
End With
With MTD.Range("AJ2")
lastrow = .Offset(0, -14).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IFERROR(INDEX(CONTRACTSTATUS,MATCH(USERNAME,MODTRACKERID,0)),""Not Found"")"
.Value = .Value
End With
End With
With MTD.Range("AK2")
lastrow = .Offset(0, -15).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IF(B:B=""RQI_PROJS"",""RQI"",IF(B:B=""ADMIN"",""ADMIN"",IF(AG:AG=""CME"",""CME "",IF(I:I="""",INDEX(PGROUP,MATCH(PROJPIV,Planalias,0)),I:I))))"
.Value = .Value
End With
End With
With MTD.Range("AL2")
lastrow = .Offset(0, -16).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=Year(TDATE)"
.Value = .Value
End With
End With
With MTD.Range("AM2")
lastrow = .Offset(0, -16).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IFERROR(IF(ISNUMBER(SEARCH(""*2X*"",Table_TIMESHEET[@ProjectType]))=TRUE,""E400"",IF(ISNUMBER(SEARCH(""*3X*"",Table_TIMESHEET[@ProjectType]))=TRUE,""E500"",IF(ISNUMBER(SEARCH(""*NF*"",Table_TIMESHEET[@ProjectType]))=TRUE,""New Flyer"",IF(ISNUMBER(SEARCH(""*_SD*"",Table_TIMESHEET[@ProjectType]))=TRUE,""E200"",IF(ISNUMBER(SEARCH(""*_CO*"",Table_TIMESHEET[@ProjectType]))=TRUE,""Coach"",IF(ISNUMBER(SEARCH(""*RQ*"",Table_TIMESHEET[@ProjPivot])),""RQI"",INDEX(PGROUP,MATCH(PROJPIV,Planalias,0)))))))),"""")"
.Value = .Value
End With
End With
With MTD.Range("AN2")
lastrow = .Offset(0, -17).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IFERROR(IF(Table_TIMESHEET[@Project]=""CONTRACT"",F:F,IF(AG:AG=""CME"",Table_TIMESHEET[ProjPivot],AI:AI)),"""")"
.Value = .Value
End With
End With
With MTD.Range("AO2")
lastrow = .Offset(0, -18).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IF([@[Contract Type]]<>""NPD "","""",INDEX(LISTS!C:C,MATCH(Tech_Dept2,PTYPE,0))&"" ""&INDEX('Total Hours Booked'!F:F,MATCH(USERNAME,MODTRACKERID,0)))"
.Value = .Value
End With
End With
With MTD.Range("AP2")
lastrow = .Offset(0, -19).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=INDEX('Total Hours Booked'!C:C,MATCH(USERNAME,MODTRACKERID,0))"
.Value = .Value
End With
End With
With MTD.Range("AS2")
lastrow = .Offset(0, -22).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=Datechg-Tdate"
.Value = .Value
End With
End With
With MTD.Range("AQ2")
lastrow = .Offset(0, -20).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IF(TIMELAG<0,""Prebooked"",IF(AND(TIMELAG>=1,TIMELAG<=8),""Up to 8 days"",IF(AND(TIMELAG>8,TIMELAG<=14),""8 to 14 days"",IF(AND(TIMELAG>14,TIMELAG<=21),""Up to 21 Days"",IF(AND(TIMELAG>21,TIMELAG<=28),""Up to 28 days"",IF(TIMELAG>28,""More than 28 Days"",""OK""))))))"
.Value = .Value
End With
End With
With MTD.Range("AR2")
lastrow = .Offset(0, -21).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IF(AND(TIMELAG<=-1,TIMELAG>=-8),""Up to 8 days"",IF(AND(TIMELAG<-8,TIMELAG>=-14),""8 to 14 days"",IF(AND(TIMELAG<-14,TIMELAG>=-21),""Up to 21 Days"",IF(AND(TIMELAG<-21,TIMELAG>=-28),""Up to 28 days"",IF(TIMELAG<-28,""More than 28 Days"","""")))))"
.Value = .Value
End With
End With
' =[@Datechg]-[@Tdate]
'Update Total Hours sheet
With THB.Range("P2")
lastrow = .Offset(0, -15).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=SUMIF(USERNAME,MODTRACKERID,TOTALHRS)"
.Value = .Value
End With
End With
With THB.Range("Q2")
lastrow = .Offset(0, -16).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IF(AND(GROUP=""HC"",MODTOTAL>0),1,MODTOTAL/AVAILHOURS)"
.Value = .Value
End With
End With
With THB.Range("R2")
lastrow = .Offset(0, -17).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IFERROR(IF(AND(GROUP=""HC"",TIMESHEET=""Yes"",[e-Technical Department]=""""),""Generic Offshore"",IF(AND(GROUP=""HC"",TIMESHEET=""Yes""),INDEX(OFFSHOREALIAS,MATCH(TECHDEPT,OFFSHORETD,0)),INDEX(PTYPEALIAS,MATCH( TECHDEPT,PTYPE,0)))),""Not Found"")"
.Value = .Value
End With
End With
With THB.Range("S2")
lastrow = .Offset(0, -18).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IF(TIMESHEET<>""Yes"",""EXCLUDED"",IF(MODTRACKERID=""NA"",MODTRACKERID,IF(GROUP=""Offshore"",""Offshore"",IF(ISNUMBER(MATCH(MODTRACKERID,USERNAME,0))=TRUE,((INDEX(USERNAME,MATCH(MO DTRACKERID,USERNAME,0)))),IF(GROUP=""LEFT"",""LEFT"",IF(GROUP="""","" "",""No Time Record""))))))"
.Value = .Value
End With
End With
With THB.Range("T2")
lastrow = .Offset(0, -19).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IF(SUMIFS(TOTALHRS,RPCHECK,RPNAME,WEEKNUM,MAX(WEEKNUM))>0,SUMIFS(TOTALHRS,RPCHECK,RPNAME,WEEKNUM,MAX(WEEKNUM)),"""")"
.Value = .Value
End With
End With
With THB.Range("U2")
lastrow = .Offset(0, -20).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IF(SUMIFS(TOTALHRS,RPCHECK,RPNAME,WEEKNUM,MAX(WEEKNUM)-1)>0,SUMIFS(TOTALHRS,RPCHECK,RPNAME,WEEKNUM,MAX(WEEKNUM)-1),"""")"
.Value = .Value
End With
End With
With THB.Range("V2")
lastrow = .Offset(0, -20).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IF(SUMIFS(TOTALHRS,RPCHECK,RPNAME,WEEKNUM,MAX(WEEKNUM)-2)>0,SUMIFS(TOTALHRS,RPCHECK,RPNAME,WEEKNUM,MAX(WEEKNUM)-2),"""")"
.Value = .Value
End With
End With
With THB.Range("W2")
lastrow = .Offset(0, -20).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IF(SUMIFS(TOTALHRS,RPCHECK,RPNAME,WEEKNUM,MAX(WEEKNUM)-3)>0,SUMIFS(TOTALHRS,RPCHECK,RPNAME,WEEKNUM,MAX(WEEKNUM)-3),"""")"
.Value = .Value
End With
End With
With THB.Range("X2")
lastrow = .Offset(0, -20).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=NETWORKDAYS(MIN(TDATE),MAX(TDATE))*7.4"
.Value = .Value
End With
End With
MsgBox ("Data Update Complete")
Application.ScreenUpdating = True
End Sub
The effect is that sometimes when I run the code (usually down about 7k rows and across 24) it misses rows - not always the same ones and it can process huge chunks (eg. hundreds or thousands of lines) and then skip one or two rows before it starts again which result in all my calculations being out. However, it doesn't happen all the time and only appears to have been happening in the last few weeks for a file that I've been using for the last year so I'm assuming that it's not the file or the code causing the problem.
What could be the causes of this? Are there any fail safes I c an build into the code (below) to check and see if the file is available to process and if not wait?
Sub update_all_data()
Dim lastrow As Long
Dim WS As Worksheet
Dim THB As Worksheet
Dim MTD As Worksheet
Set MTD = Sheets("MODTRACKER DATA")
Set THB = Sheets("Total Hours Booked")
Application.ScreenUpdating = False
'clear all auto filters
MTD.ListObjects(1).AutoFilter.ShowAllData
THB.ListObjects(1).AutoFilter.ShowAllData
'Update Columns AB:AM by inserting formulas and then pasting the result
With MTD.Columns("AD:AS").ClearContents
MTD.Range("AD1").FormulaR1C1 = "Resource Pool Check"
MTD.Range("AE1").FormulaR1C1 = "Month_"
MTD.Range("AF1").FormulaR1C1 = "Week_"
MTD.Range("AG1").FormulaR1C1 = "Tech Dept"
MTD.Range("AH1").FormulaR1C1 = "Resource Type"
MTD.Range("AI1").FormulaR1C1 = "Contract Name"
MTD.Range("AJ1").FormulaR1C1 = "Resource Contract"
MTD.Range("AK1").FormulaR1C1 = "Contract Type"
MTD.Range("AL1").FormulaR1C1 = "Year_"
MTD.Range("AM1").FormulaR1C1 = "Platform"
MTD.Range("AN1").FormulaR1C1 = "Parent Contract"
MTD.Range("AO1").FormulaR1C1 = "Role"
MTD.Range("AP1").FormulaR1C1 = "HoD"
MTD.Range("AQ1").FormulaR1C1 = "Booking Status"
MTD.Range("AR1").FormulaR1C1 = "PREBOOKED TIMELAG"
MTD.Range("AS1").FormulaR1C1 = "TIMELAG"
End With
With MTD.Range("R2")
lastrow = .Offset(0, 2).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=[@SumOfNmhrs]+[@SumOfOThrs]"
.Value = .Value
End With
End With
With MTD.Range("AD2")
lastrow = .Offset(0, -8).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=iferror(INDEX(RNAME,MATCH(USERNAME,MODTRACKERID,0)),""Not Found"")"
.Value = .Value
End With
End With
With MTD.Range("AE2")
lastrow = .Offset(0, -9).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=TEXT((TDATE),""MMMM"")"
.Value = .Value
End With
End With
With MTD.Range("AF2")
lastrow = .Offset(0, -10).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=WEEKNUM(Table_TIMESHEET[@Tdate],21)"
.Value = .Value
End With
End With
With MTD.Range("AG2")
lastrow = .Offset(0, -11).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=iferror(INDEX(TECHDEPT,MATCH(USERNAME,MODTRACKERID,0)),""Not Found"")"
.Value = .Value
End With
End With
With MTD.Range("AH2")
lastrow = .Offset(0, -12).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IF(AND(ISNUMBER(SEARCH(""*Offshore*"",RPCHECK)),ISNUMBER(SEARCH(""*VA/VE*"",PROJECTTYPE))),""VA/VE"", IF(AND(ISNUMBER(SEARCH(""*Offshore*"",RPCHECK)),ISNUMBER(SEARCH(""*VCE*"", PROJECTTYPE))),""Vehicle Concepts"", IF(AND(ISNUMBER(SEARCH(""*Offshore*"",RPCHECK)),ISNUMBER(SEARCH(""*DEMO*"", PROJECTTYPE))),""Demo Vehicles"", IF(AND(ISNUMBER(SEARCH(""*Offshore*"",RPCHECK)),ISNUMBER(SEARCH(""*Electric*"",AE:AE))),INDEX(TECHDEPTALIAS,MATCH(RPCHECK,RPNAME,0)),IF(AND(ISNUMBER(SEAR CH(""*Offshore*"",RPCHECK)),ISNUMBER(SEARCH(""*NPD*"",CTYPE))),CONCATENATE(""NPD - "",INDEX(TECHDEPTALIAS,MATCH(RPCHECK,RPNAME,0))),IF(AND(ISNUMBER(SEARCH(""*Offshore*"",RPCHECK)),ISNUMBER(SEARCH(""*CME"",CTYPE))),CONCATENATE(""CME - "",INDEX(TECHDEPTALIAS,MATCH(RPCHECK,RPNAME,0))), INDEX('Total Hours Booked'!R:R,MATCH(RPCHECK,RPNAME,0))))))))"
.Value = .Value
End With
End With
With MTD.Range("AI2")
lastrow = .Offset(0, -13).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IFERROR(IF(ISBLANK(USERNAME),"""",IF(ISNUMBER(SEARCH(""*Holiday*"",TSREFDES)),""Holiday"",IF(ISNUMBER(SEARCH(""*Sickness*"",TSREFDES)),""Sickness"", IF(ISNUMBER(SEARCH(""*Absence - Other Approved*"",TSREFDES)),""Other Absence"",IF(ISNUMBER(SEARCH(""*RQ*"",F:F)),""RQI"",IF(ISNUMBER(SEARCH(""*NF_*"",Table_TIMESHEET[@ProjectType])),""New Flyer"",IF(ISNUMBER(SEARCH(""*SALES_*"",Table_TIMESHEET[@ProjectType])),""CME"",(INDEX(ALIAS,MATCH(PROJPIV,Planalias,0)))))))))),""Not Found"")"
.Value = .Value
End With
End With
With MTD.Range("AJ2")
lastrow = .Offset(0, -14).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IFERROR(INDEX(CONTRACTSTATUS,MATCH(USERNAME,MODTRACKERID,0)),""Not Found"")"
.Value = .Value
End With
End With
With MTD.Range("AK2")
lastrow = .Offset(0, -15).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IF(B:B=""RQI_PROJS"",""RQI"",IF(B:B=""ADMIN"",""ADMIN"",IF(AG:AG=""CME"",""CME "",IF(I:I="""",INDEX(PGROUP,MATCH(PROJPIV,Planalias,0)),I:I))))"
.Value = .Value
End With
End With
With MTD.Range("AL2")
lastrow = .Offset(0, -16).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=Year(TDATE)"
.Value = .Value
End With
End With
With MTD.Range("AM2")
lastrow = .Offset(0, -16).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IFERROR(IF(ISNUMBER(SEARCH(""*2X*"",Table_TIMESHEET[@ProjectType]))=TRUE,""E400"",IF(ISNUMBER(SEARCH(""*3X*"",Table_TIMESHEET[@ProjectType]))=TRUE,""E500"",IF(ISNUMBER(SEARCH(""*NF*"",Table_TIMESHEET[@ProjectType]))=TRUE,""New Flyer"",IF(ISNUMBER(SEARCH(""*_SD*"",Table_TIMESHEET[@ProjectType]))=TRUE,""E200"",IF(ISNUMBER(SEARCH(""*_CO*"",Table_TIMESHEET[@ProjectType]))=TRUE,""Coach"",IF(ISNUMBER(SEARCH(""*RQ*"",Table_TIMESHEET[@ProjPivot])),""RQI"",INDEX(PGROUP,MATCH(PROJPIV,Planalias,0)))))))),"""")"
.Value = .Value
End With
End With
With MTD.Range("AN2")
lastrow = .Offset(0, -17).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IFERROR(IF(Table_TIMESHEET[@Project]=""CONTRACT"",F:F,IF(AG:AG=""CME"",Table_TIMESHEET[ProjPivot],AI:AI)),"""")"
.Value = .Value
End With
End With
With MTD.Range("AO2")
lastrow = .Offset(0, -18).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IF([@[Contract Type]]<>""NPD "","""",INDEX(LISTS!C:C,MATCH(Tech_Dept2,PTYPE,0))&"" ""&INDEX('Total Hours Booked'!F:F,MATCH(USERNAME,MODTRACKERID,0)))"
.Value = .Value
End With
End With
With MTD.Range("AP2")
lastrow = .Offset(0, -19).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=INDEX('Total Hours Booked'!C:C,MATCH(USERNAME,MODTRACKERID,0))"
.Value = .Value
End With
End With
With MTD.Range("AS2")
lastrow = .Offset(0, -22).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=Datechg-Tdate"
.Value = .Value
End With
End With
With MTD.Range("AQ2")
lastrow = .Offset(0, -20).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IF(TIMELAG<0,""Prebooked"",IF(AND(TIMELAG>=1,TIMELAG<=8),""Up to 8 days"",IF(AND(TIMELAG>8,TIMELAG<=14),""8 to 14 days"",IF(AND(TIMELAG>14,TIMELAG<=21),""Up to 21 Days"",IF(AND(TIMELAG>21,TIMELAG<=28),""Up to 28 days"",IF(TIMELAG>28,""More than 28 Days"",""OK""))))))"
.Value = .Value
End With
End With
With MTD.Range("AR2")
lastrow = .Offset(0, -21).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IF(AND(TIMELAG<=-1,TIMELAG>=-8),""Up to 8 days"",IF(AND(TIMELAG<-8,TIMELAG>=-14),""8 to 14 days"",IF(AND(TIMELAG<-14,TIMELAG>=-21),""Up to 21 Days"",IF(AND(TIMELAG<-21,TIMELAG>=-28),""Up to 28 days"",IF(TIMELAG<-28,""More than 28 Days"","""")))))"
.Value = .Value
End With
End With
' =[@Datechg]-[@Tdate]
'Update Total Hours sheet
With THB.Range("P2")
lastrow = .Offset(0, -15).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=SUMIF(USERNAME,MODTRACKERID,TOTALHRS)"
.Value = .Value
End With
End With
With THB.Range("Q2")
lastrow = .Offset(0, -16).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IF(AND(GROUP=""HC"",MODTOTAL>0),1,MODTOTAL/AVAILHOURS)"
.Value = .Value
End With
End With
With THB.Range("R2")
lastrow = .Offset(0, -17).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IFERROR(IF(AND(GROUP=""HC"",TIMESHEET=""Yes"",[e-Technical Department]=""""),""Generic Offshore"",IF(AND(GROUP=""HC"",TIMESHEET=""Yes""),INDEX(OFFSHOREALIAS,MATCH(TECHDEPT,OFFSHORETD,0)),INDEX(PTYPEALIAS,MATCH( TECHDEPT,PTYPE,0)))),""Not Found"")"
.Value = .Value
End With
End With
With THB.Range("S2")
lastrow = .Offset(0, -18).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IF(TIMESHEET<>""Yes"",""EXCLUDED"",IF(MODTRACKERID=""NA"",MODTRACKERID,IF(GROUP=""Offshore"",""Offshore"",IF(ISNUMBER(MATCH(MODTRACKERID,USERNAME,0))=TRUE,((INDEX(USERNAME,MATCH(MO DTRACKERID,USERNAME,0)))),IF(GROUP=""LEFT"",""LEFT"",IF(GROUP="""","" "",""No Time Record""))))))"
.Value = .Value
End With
End With
With THB.Range("T2")
lastrow = .Offset(0, -19).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IF(SUMIFS(TOTALHRS,RPCHECK,RPNAME,WEEKNUM,MAX(WEEKNUM))>0,SUMIFS(TOTALHRS,RPCHECK,RPNAME,WEEKNUM,MAX(WEEKNUM)),"""")"
.Value = .Value
End With
End With
With THB.Range("U2")
lastrow = .Offset(0, -20).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IF(SUMIFS(TOTALHRS,RPCHECK,RPNAME,WEEKNUM,MAX(WEEKNUM)-1)>0,SUMIFS(TOTALHRS,RPCHECK,RPNAME,WEEKNUM,MAX(WEEKNUM)-1),"""")"
.Value = .Value
End With
End With
With THB.Range("V2")
lastrow = .Offset(0, -20).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IF(SUMIFS(TOTALHRS,RPCHECK,RPNAME,WEEKNUM,MAX(WEEKNUM)-2)>0,SUMIFS(TOTALHRS,RPCHECK,RPNAME,WEEKNUM,MAX(WEEKNUM)-2),"""")"
.Value = .Value
End With
End With
With THB.Range("W2")
lastrow = .Offset(0, -20).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=IF(SUMIFS(TOTALHRS,RPCHECK,RPNAME,WEEKNUM,MAX(WEEKNUM)-3)>0,SUMIFS(TOTALHRS,RPCHECK,RPNAME,WEEKNUM,MAX(WEEKNUM)-3),"""")"
.Value = .Value
End With
End With
With THB.Range("X2")
lastrow = .Offset(0, -20).End(xlDown).Row
With .Resize(lastrow - .Row + 1)
.Formula = "=NETWORKDAYS(MIN(TDATE),MAX(TDATE))*7.4"
.Value = .Value
End With
End With
MsgBox ("Data Update Complete")
Application.ScreenUpdating = True
End Sub