Consulting

Results 1 to 5 of 5

Thread: Can files residing on a slow network cause VBA to 'skip'?

  1. 11-02-2016, 12:43 AM #1
    purple_ninja
    purple_ninja is offline
    VBAX Regular
    Joined
    Feb 2016
    Posts
    15
    Location

    Can files residing on a slow network cause VBA to 'skip'?

    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(SEARCH(""*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(MODTRACKERID,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
    Reply With Quote Reply With Quote
  2. 11-02-2016, 02:36 AM #2
    snb
    snb is offline
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,646
    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).
    Last edited by snb; 11-02-2016 at 05:08 AM.
    more suggestions ...
    Reply With Quote Reply With Quote
  3. 11-02-2016, 03:57 AM #3
    purple_ninja
    purple_ninja is offline
    VBAX Regular
    Joined
    Feb 2016
    Posts
    15
    Location
    I'm sorry you've lost me?
    Reply With Quote Reply With Quote
  4. 11-02-2016, 05:08 AM #4
    snb
    snb is offline
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,646
    Please reread my post.
    more suggestions ...
    Reply With Quote Reply With Quote
  5. 11-02-2016, 05:47 AM #5
    Paul_Hossler
    Paul_Hossler is offline
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,729
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules