PDA

View Full Version : Can files residing on a slow network cause VBA to 'skip'?



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

snb
11-02-2016, 02:36 AM
Why don't you use a template file from which you can copy AD1:AS40000 and P2:X2 ?

(or the other way around : a template in which you can copy the data from your active worksheet).

purple_ninja
11-02-2016, 03:57 AM
I'm sorry you've lost me?

snb
11-02-2016, 05:08 AM
Please reread my post.

Paul_Hossler
11-02-2016, 05:47 AM
I assume the file is not shared and multiple users can update it

Try turning calculation off at the beginning and on at the end

Try adding a DoEvents after you add each formula