none
Excel VBA - Sub Restarts Unexpectedly RRS feed

  • Question

  • While I'm not entirely new to VBA development I'm certainly not an expert.  I've encountered a rather perplexing issue with a small VBA sub that I've started to develop under Excel (2016 via 365).

    After executing a simple EntireColumn.Insert the execution path restarts at the beginning of the Sub.  No error messages.   I've removed the insert functionality and the same happens later in the code in the For loop.

    Any help on how to diagnose would be much appreciated.  I've highlighted the offensive line with a comment tagged with "LOOK HERE".

    many thanks!

    Code:

    Sub PrepareTheData()
        
        Dim lCol As Integer, rCount As Long
        Dim nameStringEndsAt As Integer, assignedToCol As Integer
        Dim workingSheetName As Worksheet
        Dim lRow As Long
        Dim searchString As String
        Dim Pivot As PivotTable
        
            
        Set workingSheetName = ActiveWorkbook.Worksheets("BDIE Tasks by Resource Query")
            
        lRow = workingSheetName.Cells(Rows.Count, 1).End(xlUp).Row
        assignedToCol = 6
        
        'Check if assigned resource name column exists and delete it if needed
        If workingSheetName.Cells(1, assignedToCol + 3).Value = "Assigned Resource Name" Then
        
            workingSheetName.Columns(assignedToCol + 3).EntireColumn.Delete
        
        End If
         
        'Insert a column for the assigned resource name
        workingSheetName.Range("J:J").EntireColumn.Insert   '<--- LOOK HERE:  This row causes restart right at the beginning of the Sub
        
        workingSheetName.Cells(1, assignedToCol + 3).Value = "Assigned Resource Name"
        
        For rCount = 2 To lRow
        
            'locate the name
            searchString = workingSheetName.Cells(rCount, assignedToCol)
            
            If Len(searchString) > 0 Then
                
                nameStringEndsAt = InStr(searchString, "<") - 2
        
                workingSheetName.Cells(rCount, assignedToCol + 4).Value = Left(searchString, nameStringEndsAt)
                workingSheetName.Cells(rCount, assignedToCol + 5).Formula = "=VLOOKUP(I" & CStr(rCount) & ",Table110,2,FALSE)"
                workingSheetName.Cells(rCount, assignedToCol + 6).Formula = "=VLOOKUP(I" & CStr(rCount) & ",Table110,4,FALSE)"
                
            Else
                workingSheetName.Cells(rCount, assignedToCol + 4).Value = "Unassigned"
                workingSheetName.Cells(rCount, assignedToCol + 5).Value = ""
                workingSheetName.Cells(rCount, assignedToCol + 6).Value = ""
                
            End If
                            
        Next rCount
        
        workingSheetName.Range("A:A").EntireColumn.AutoFit
        
        'Refresh the pivot tables
        For Each Pivot In Worksheets("Work Content by Person").PivotTables
            Pivot.RefreshTable
        Next

    End Sub

    Thursday, January 9, 2020 1:37 PM

All replies

  • Maybe this Sub is called from some event handler (Worksheet_Change, for example)?


    • Edited by Viorel_MVP Thursday, January 9, 2020 7:03 PM
    Thursday, January 9, 2020 7:02 PM
  • Thanks for the reply.  Sub not called from any event other than an ActiveX control button.  No other code in the entire workbook.  Thanks for the thought...  
    Thursday, January 9, 2020 8:17 PM
  • To:  Brette

    I believe this section of code...

        If workingSheetName.Cells(1, assignedToCol + 3).Value = "Assigned Resource Name" Then
           workingSheetName.Columns(assignedToCol + 3).EntireColumn.Delete
        End If
        'Insert a column for the assigned resource name
        workingSheetName.Range("J:J").EntireColumn.Insert   '<--- LOOK HERE:  This row causes restart right at the beginning of the Sub
        workingSheetName.Cells(1, assignedToCol + 3).Value = "Assigned Resource Name"

    should look like...
        If workingSheetName.Cells(1, assignedToCol + 3).Value = "Assigned Resource Name" Then
           workingSheetName.Columns(assignedToCol + 3).EntireColumn.Delete
           workingSheetName.Range("I:I").EntireColumn.Insert   'note i:i not J:J
           workingSheetName.Cells(1, assignedToCol + 3).Value = "Assigned Resource Name"
        End If

    But even better, just clear the existing column, rename the first cell and continue
    '---


    Excel programs (now free) at MediaFire...
    The Custom_Functions add-in has 20+ new Excel functions including the X_vLookUp function
    (no ads when downloading)
    Friday, January 10, 2020 8:49 PM