Excel VBA skipping the same line of code, and I'd really prefer that it didn't

Question

I'm a first-time VBA-coder, so my code is probably littered with inefficiencies. I've cobbled this code together from a variety of internet sources, but cannot find a suitable answer to this problem which is causing me a major head-ache.

In short, the code takes raw data and moves it into a sheet designed for the month for that report. After adding columns and formulae, the macro will filter the manipulated raw data and the filtered results will be populated copied into a user-interface sheet (Queries) where drop-down data validation will be applied (to ensure a consistent response) is provided, along with conditional formatting to highlight the cells which has are affected.

And that's it. That's all this code needs to do. But here's the sticking point: every single time I run this code, at least one line of code is skipped. I know the code works, because I've managed to replicate the code on a blank worksheet and (eventually) all lines of code are run. But no matter how many times I try, I can't get the code to run fully in my live sheet.

I should point out that I can F8 through the code and (on the whole) it runs perfectly.

I've provided my code here for the experts to review; perhaps someone can provide advice on fixing the problem areas so each line is always run. I'm also open to improving my code if there's any suggestions expert-coders might make.

Thanks in advance

Sub AnalyseDataButton()

Dim Month As String
   Month = Worksheets("Home").Range("B1")
Dim HlastRow As Long
   HlastRow = Worksheets("Home").Range("A" & Rows.Count).End(xlUp).Row
Dim IlastRow
   IlastRow = Worksheets(Month).Range("A" & Rows.Count).End(xlUp).Row
Dim lastRow As Long
   lastRow = Worksheets(Month).Range("K" & Rows.Count).End(xlUp).Row
Dim QlastRow As Long
   QlastRow = Worksheets("Queries").Range("A" & Rows.Count).End(xlUp).Row

  Worksheets("Home").Calculate

'Validating all data has been added
   If (Worksheets("Home").Range("A15") = "" Or Worksheets("Home").Range("K15") = "" Or Worksheets("Home").Range("U15") = "") Then
       MsgBox "Please ensure you have added all three reports", vbExclamation + vbOKOnly, "Unable to run reports"
   Else
'Complete all actions before showing results
   Application.ScreenUpdating = False


'Prepare the September sheet for data
   Worksheets(Month).UsedRange.ClearContents
'Move the data from Home to September, then clear the data from Home
   Worksheets("Home").Range("A15").Select
   Worksheets("Home").Range("A15:AA" & HlastRow).Copy Destination:=Worksheets(Month).Range("A1")
'Add additional columns as needed
   Worksheets(Month).Range("T1:W1").EntireColumn.Insert

'INCIDENTS
'Apply Header to Actual Elapsed
   Worksheets(Month).Range("T1") = "ActualElapsed"
'Apply Formula to T2
   Worksheets(Month).Range("T2") = "=ROUNDUP(VLOOKUP(K2,$A$2:$I" & IlastRow & ",6,FALSE)/86400,0)"
'Copy Formula down to last row
   Worksheets(Month).Range("T2").AutoFill Destination:=Worksheets(Month).Range("T2:T" & lastRow)
'Apply Header to Actual Met
   Worksheets(Month).Range("U1") = "ActualMet"
'Apply Formula to U2
   Worksheets(Month).Range("U2") = "=IF(NETWORKDAYS(M2,R2,HOLIDAYS)-1+MOD(M2,1)-MOD(R2,1)>5,""missed"",""met"")"
'Copy Formula down to last row
   Worksheets(Month).Range("U2").AutoFill Destination:=Worksheets(Month).Range("U2:U" & lastRow)
'Apply Header to Business Met
   Worksheets(Month).Range("V1") = "BusinessMet"
'Apply Formula to V2
   Worksheets(Month).Range("V2") = "=IF(VLOOKUP(K2,$A$2:$H$" & IlastRow & ",8,FALSE)>432000,""missed"",""met"")"
'Copy Formula down to last row
   Worksheets(Month).Range("V2").AutoFill Destination:=Worksheets(Month).Range("V2:V" & lastRow)
'Remove any Wrapped text
   Worksheets(Month).Cells.WrapText = False
'Add Justification header on the Month tab
   Worksheets(Month).Range("W1").Value = "Justification"
'Determine the list of query items
   Worksheets("Queries").UsedRange.Clear
   Worksheets(Month).Calculate
   Worksheets(Month).Range("$K$1:$V$" & lastRow).AutoFilter Field:=11, Criteria1:="=missed"
   Worksheets(Month).Range("K1:M" & Cells(Rows.Count, "K").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy Worksheets("Queries").Range("A5")
'Add data validation to the ActualMet
'### THIS STEP IS REGULARLY MISSED
   With Worksheets("Queries").Range("F6").Validation
   .Delete
   .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Dates!$G$1:$G$2"
   .IgnoreBlank = True
   .InCellDropdown = True
   .InputTitle = ""
   .InputMessage = ""
   .InputMessage = ""
   .ErrorMessage = ""
   .ShowInput = True
   .ShowError = True
   End With
'Add data validation to the ActualMet
'### THIS STEP IS REGULARLY MISSED
   Worksheets("Queries").Range("F6").AutoFill Destination:=Worksheets("Queries").Range("F6:F" & QlastRow)
'Continue to move data to the Queries sheet
   Worksheets(Month).Range("R1:R" & Cells(Rows.Count, "K").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy Worksheets("Queries").Range("D5")
   Worksheets(Month).Range("T1:V" & Cells(Rows.Count, "K").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy Worksheets("Queries").Range("E5")
   Worksheets("Queries").Cells.WrapText = False
   Worksheets("Queries").Columns("A:I").EntireColumn.AutoFit
   Worksheets(Month).AutoFilterMode = False
   Worksheets("Queries").Range("H5") = "Reasons for breaching SLA"
'Add data validation to the Justification
'### THIS STEP IS SOMETIMES MISSED
'### WHEN THIS STEP IS MISSED, THE RESULTS SHOW THE MACRO STARTED ON RANGE("H5")
   With Worksheets("Queries").Range("H6").Validation
   .Delete
   .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Dates!$J$1:$J$5"
   .IgnoreBlank = True
   .InCellDropdown = True
   .InputTitle = ""
   .InputMessage = ""
   .InputMessage = ""
   .ErrorMessage = ""
   .ShowInput = True
   .ShowError = True
   End With
   Worksheets("Queries").Range("H6").AutoFill Destination:=Worksheets("Queries").Range("H6:H" & QlastRow)
'Move to Queries sheet
   MsgBox "Thank you for uploading data." & vbNewLine & "" & vbNewLine & "*** INCIDENT TASKS ***" & vbNewLine & "You will now be shown the Incident Tasks which missed SLA." & vbNewLine & "Please provide justification or make amendments as required.", vbInformation, "Thank You"
   Worksheets("Queries").Activate
'Header for Queries sheet
   Worksheets("Queries").Range("A4").FormulaR1C1 = "List of INCIDENT TASKS to be reviewed."
   Worksheets("Queries").Range("A4:H4").Select
   With Selection
       .HorizontalAlignment = xlCenter
       .VerticalAlignment = xlBottom
       .WrapText = False
       .Orientation = 0
       .AddIndent = False
       .IndentLevel = 0
       .ShrinkToFit = False
       .ReadingOrder = xlContext
       .MergeCells = False
   End With
   Selection.Merge
   With Selection.Font
       .Name = "Calibri"
       .Size = 20
       .Strikethrough = False
       .Superscript = False
       .Subscript = False
       .OutlineFont = False
       .Shadow = False
       .Underline = xlUnderlineStyleNone
       .ThemeColor = xlThemeColorLight1
       .TintAndShade = 0
       .ThemeFont = xlThemeFontMinor
   End With
   With Selection.Interior
       .Pattern = xlSolid
       .PatternColorIndex = xlAutomatic
       .Color = 13532366
       .TintAndShade = 0
       .PatternTintAndShade = 0
   End With
'Add conditional formatting
'### THIS STEP IS SOMETIMES PERFORMED ON RANGE ("D1") OF THE QUERIES SHEET
   Worksheets("Queries").Cells.FormatConditions.Delete
   Worksheets("Queries").Range("H6:H" & QlastRow).Select
   Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
       "=AND(F6=""missed"",H6<>"""")"
   Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
   With Selection.FormatConditions(1).Interior
       .PatternColorIndex = xlAutomatic
       .Color = RGB(0, 176, 80)
       .TintAndShade = 0
   End With
   Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
       "=AND(F6=""missed"",H6="""")"
   Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
   With Selection.FormatConditions(1).Interior
       .PatternColorIndex = xlAutomatic
       .Color = RGB(255, 0, 0)
       .TintAndShade = 0
   End With
   Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
       "=AND(F6=""met"",H6<>"""")"
   Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
   With Selection.FormatConditions(1).Interior
       .PatternColorIndex = xlAutomatic
       .Color = RGB(0, 176, 80)
       .TintAndShade = 0
   End With
   Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
       "=AND(F6=""met"",H6="""")"
   Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
   With Selection.FormatConditions(1).Interior
       .PatternColorIndex = xlAutomatic
       .Color = RGB(198, 89, 17)
       .TintAndShade = 0
   End With
   Selection.FormatConditions(1).StopIfTrue = False
   Worksheets("Queries").Range("F6:F" & QlastRow).Select
   Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
       "=AND(F6=""met"",H6<>"""")"
   Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
   With Selection.FormatConditions(1).Interior
       .PatternColorIndex = xlAutomatic
       .Color = RGB(255, 192, 0)
       .TintAndShade = 0
   End With
   Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
       "=AND(F6=""met"",H6="""")"
   Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
   With Selection.FormatConditions(1).Interior
       .PatternColorIndex = xlAutomatic
       .Color = RGB(198, 89, 17)
       .TintAndShade = 0
   End With
'Show results now the macro has run
   Worksheets("Queries").Range("H6").Select
   Application.ScreenUpdating = True
   MsgBox "Please review each task and select the reason for breaching SLA.", vbExclamation, "Review the Incident Tasks outside of SLA"

End If
End Sub

How do you determine which line was "skipped"? Is it always the same line or does it vary? — riskypenguin, Nov 07 '19 at 11:10
Often the same line(s) are skipped. In my code, I've added comments showing which lines are Regularly and Sometimes skipped. The comments start '### for ease of review. — Marc, Nov 07 '19 at 11:15
My first assumption would be that the multiple `.Select` statements are at least part of the problem, you will benefit from reading [How To Avoid Using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). If you want to check if certain lines are executed or not (lines are simply randomly skipped) I would recommend inserting breakpoints and/ or inserting `Debug.Print` statements that show you the values of variables you're handling etc. — riskypenguin, Nov 07 '19 at 11:21
Thanks for that. I will look to reduce the `.Select` statements if I can. I'll also do some investigation into the `Debug.Print` which I've heard about, but haven't explored in detail. I tried the breakpoints yesterday and they didn't prove useful. I'd really like it if VBA would read and action every line of code - do you have any idea why it does skip? — Marc, Nov 07 '19 at 11:32
VBA executes every line of code, under certain circumstances however some lines of code might not have the desired effect or any effect at all. You usually don't need any `.Select` statements, removing them entirely should be the goal. — riskypenguin, Nov 07 '19 at 11:35
Ok, I've removed all but the final `.Select` and `Selection` statements, and replaced them with the actual path. However exactly the same lines are missed: `With Worksheets("Queries").Range("F6").Validation` and the dragging the validation down: `Worksheets("Queries").Range("F6").AutoFill Destination:=Worksheets("Queries").Range("F6:F" & QlastRow)`. The other validation (column H) works absolutely fine. I feel like I'm banging my head against a brick wall. — Marc, Nov 07 '19 at 12:19
I have tried moving the problem-code to later on, and this works. But I really need it where it is, to help with the copy/paste which happens in the next line. — Marc, Nov 07 '19 at 12:20
This is not specific to your issue; if you indent your code properly it would be easier to read, and you would shorten your code if you write your formulas directly to the range. — GMalc, Nov 07 '19 at 12:24
@GMalc thanks. Like I said in my problem statement I've never written VBA before, so am happy to learn. I can move every range into a `Dim As Range` segment (although I do use a lot of different ranges, so that will take some thought). Would that prevent the skipping of lines which I'm experiencing? — Marc, Nov 07 '19 at 12:29

Excel VBA skipping the same line of code, and I'd really prefer that it didn't

0 Answers0