VBA does not update all rows in spreadsheet

O

orangebloss

Board Regular
Joined
Jun 5, 2013
Messages
51
Office Version
  1. 365
Platform
  1. Windows
I have a large 7k line plus spreadsheet that uses VBA to add several columns to it and then pastes the resulting value. However, I've noticed lately that it sometimes doesn't put all the values in and skips some rows. It isn't consistent on the rows it's missing (e.g. first 368 lines are fine, then it misses 369, 379, 3066 , and it's never the same line twice)

I'm assuming that the issue is around the amount of data and the speed that the vba is running at? Do I need to slow it down? The whole point of running the vba was to increase the speed of the processing. Sometimes it does work though so I'm completely confused as to why it would sometimes work and others not?

Code: 
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
'THIS IS THE COLUMN THAT IS NOTICABLY WRONG          
  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
  'END OF COLUMN          
            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
           
         
 
 '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
 
Last edited:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
You must log in or register to reply here.

Similar threads

H
  • Solved
Formula Issue when Converting to VBA
Replies
3
Views
336
MARK858
MARK858
Guna13
  • Question
Help to Optimize Macro Code for Faster Performance
Replies
0
Views
748
Guna13
Guna13
S
  • Solved
Display data of Specific Sheets on Listbox Userform
2
Replies
11
Views
857
Akuini
Akuini
R
  • Question
VBA loop through date type mismatch
2
Replies
19
Views
774
Georgiboy
Georgiboy
A
  • Question
prevent active cell into cells contains formulas for two columns
Replies
0
Views
233
abdo meghari
A

Forum statistics

Threads
1,214,819
Messages
6,121,739
Members
449,050
Latest member
excelknuckles

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top