Certain actions not completed by code

[gravanoc]

I have a script that makes some formatting changes to a few files, and returns a count of things like number of duplicate items, remaining items, etc. I have an option for the user to choose whether to keep the affected files open or not after running the script. If they choose the option to close the files, several formatting operations are left undone & there are no counts, despite the fact that they are saved after the operations in the script. If the files are left open this does not happen, or if I walk through the last few affected lines step by step, then it works as intended.

Why is this happening?

     ... earlier code omitted
    lFiltIndex = loCalls.ListColumns.Count
    lCount(3) = WorksheetFunction.CountIf(loCalls.ListColumns(loCalls.ListColumns.Count).Range, "INVALID")
    lCount(4) = WorksheetFunction.CountIf(loCalls.ListColumns(loCalls.ListColumns.Count).Range, "VALID")
    
    loCalls.Range.AutoFilter Field:=lFiltIndex, Criteria1:="VALID"
    loCalls.Range.Sort Key1:=loCalls.ListColumns(21).DataBodyRange, Order1:=xlDescending
    loCalls.Range.Columns("P:U").SpecialCells(xlCellTypeVisible).Copy
    
    rngCheck(3).Cells(1, 1).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
    wksCalls(3).Activate
    
   // For loop actions do not work.
    For i = 0 To rngCheck(3).CurrentRegion.Columns.Count - 1
        
        If IsNumeric(Right(rngCheck(3).Cells(1, i + 1).Value, 1)) Then
            rngCheck(3).Cells(1, i + 1).Value = WorksheetFunction.Proper(Left(rngCheck(3).Cells(1, i + 1).Value, Len(rngCheck(3).Cells(1, i + 1).Value) - 1))
        Else
            rngCheck(3).Cells(1, i + 1).Value = WorksheetFunction.Proper(rngCheck(3).Cells(1, i + 1).Value)
        End If
        
    Next i
    
    // These two lines fail as well
    rngCheck(3).CurrentRegion.Columns.AutoFit
    rngCheck(3).CurrentRegion.Value = Evaluate("=PROPER(" & rngCheck(3).CurrentRegion.Address & ")")
    
   // These work, they do save the files.
    wbCalls(3).SaveAs ("Calls Log Results.csv"), 62
    wbRes.SaveAs ("Calls Log Breakdown.csv"), 62
    
    On Error Resume Next
    wksRes.Activate
    Application.Wait Now + TimeSerial(0, 0, 1)
    On Error GoTo 0
    
    // For loop actions do not work.
    For i = 0 To UBound(lCount)
        rngRes.Cells(2, i + 1).Value = lCount(i)
    Next i
    
    // Everything else after this works fine.
    sMsg = "Process completed successfully!"
ExitEarly:

    If Not bCheck Then
        For i = 0 To 3
            wbCalls(i).Close
        Next i
        
        wbRes.Close
    End If
    
    // This turns back on events, alerts, & screen updating.  Calculation is always automatic throughout script.
    Call EventStart
    MsgBox sMsg, vbOKOnly, "Notification"
    
End Sub

 

[NdNoviceHlp]

Maybe it's one of those timing things. Trial adding this before the section that doesn't seem to work. HTH. Dave

Dim T As Double
T = Timer
Do Until Timer - T > 1
  DoEvents
Loop

 

[gravanoc]

Maybe it's one of those timing things. Trial adding this before the section that doesn't seem to work. HTH. Dave

Dim T As Double
T = Timer
Do Until Timer - T > 1
  DoEvents
Loop

Thanks, I will.

Update: Unfortunately, it didn't work still.

 

[gravanoc]

Thanks, I will.

Update: Unfortunately, it didn't work still.

Ok I figured it out. I made a couple mistakes. The formatting was because I saved it as a CSV, and the counts were done after the save process. Dumb mistakes on my part.

 

[NdNoviceHlp]

Thanks for the update. There are no dumb mistakes... just moments of learning. Have a nice day. Dave