Loop runs over its last count in a code with error handling?

L

Lil2606

Board Regular
Joined
Jan 29, 2019
Messages
79
Hi all,

My problem is; that the code works like a charm, no run-time errors or any of the sorts, there is error handling in my code with user-forms (with simple input-boxes it was confusing) they work great too, except in the last row instead of my loop ending, it runs over to the next row and if I input something in the user-forms then it will be a new row and then carries on to the next row, and becomes an endless loop. I can only exit the loop if I hit no for the msgbox, then it starts my next loop like an angel, does the job again, no issues, but again tries to become an endless loop in the last row.

Anyone came across something like this before? Any ideas how to fix it or why does it happen? Could I get some help please?

I have posted this question on ExcelForum with an example worksheet, with the code in it link: https://www.excelforum.com/excel-pr...-a-code-with-error-handling-with-example.html

I'm new to VBA hence the countless repetition, but I hope it makes sense:

LONG code:
Code: 
Sub Create_Tables_Split_By_Region()

Dim m As Long
Dim D1LUV As String 'D_ata 1 L_ook U_p V_alue
Dim D1LUR As String 'D_ata 1 L_ook U_p R_esult
Dim New_Country As String
Dim Correction As Integer


For m = 2 To Sheet2.Cells.Find(what:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row


Resume_run:


D1LUV = Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Country").Range.Column)


On Error GoTo Fixit


D1LUR = WorksheetFunction.VLookup(D1LUV, Sheet1.ListObjects("LookUpTable").DataBodyRange, Sheet1.ListObjects("LookUpTable").ListColumns("Region").Range.Column, False)


On Error GoTo 0


    If D1LUR = "EAST" Then
        
        Sheet4.ListObjects("EastTable").ListRows.Add.Range(1, Sheet4.ListObjects("EastTable").ListColumns("Data").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Data").Range.Column)
        
        Sheet4.ListObjects("EastTable").DataBodyRange(Sheet4.ListObjects("EastTable").ListRows.Count, Sheet4.ListObjects("EastTable").ListColumns("Other Data").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Other Data").Range.Column)
       
        Sheet4.ListObjects("EastTable").DataBodyRange(Sheet4.ListObjects("EastTable").ListRows.Count, Sheet4.ListObjects("EastTable").ListColumns("Another Data").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Another Data").Range.Column)
        
        Sheet4.ListObjects("EastTable").DataBodyRange(Sheet4.ListObjects("EastTable").ListRows.Count, Sheet4.ListObjects("EastTable").ListColumns("Text").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Text").Range.Column)
       
        Sheet4.ListObjects("EastTable").DataBodyRange(Sheet4.ListObjects("EastTable").ListRows.Count, Sheet4.ListObjects("EastTable").ListColumns("Something").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Something").Range.Column)
       
        Sheet4.ListObjects("EastTable").DataBodyRange(Sheet4.ListObjects("EastTable").ListRows.Count, Sheet4.ListObjects("EastTable").ListColumns("Country").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Country").Range.Column)
        
        Sheet4.ListObjects("EastTable").DataBodyRange(Sheet4.ListObjects("EastTable").ListRows.Count, Sheet4.ListObjects("EastTable").ListColumns("Region").Range.Column) = _
        D1LUR
        
        Sheet4.ListObjects("EastTable").DataBodyRange(Sheet4.ListObjects("EastTable").ListRows.Count, Sheet4.ListObjects("EastTable").ListColumns("Something Else").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Something Else").Range.Column)
       
        Sheet4.ListObjects("EastTable").DataBodyRange(Sheet4.ListObjects("EastTable").ListRows.Count, Sheet4.ListObjects("EastTable").ListColumns("Number").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Number").Range.Column)
       
        Sheet4.ListObjects("EastTable").DataBodyRange(Sheet4.ListObjects("EastTable").ListRows.Count, Sheet4.ListObjects("EastTable").ListColumns("Another Note").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Another Note").Range.Column)
    
    ElseIf D1LUR = "HQ" Then
    
        Sheet4.ListObjects("HQTable").ListRows.Add.Range(1, Sheet4.ListObjects("HQTable").ListColumns("Data").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Data").Range.Column)
        
        Sheet4.ListObjects("HQTable").DataBodyRange(Sheet4.ListObjects("HQTable").ListRows.Count, Sheet4.ListObjects("HQTable").ListColumns("Other Data").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Other Data").Range.Column)
       
        Sheet4.ListObjects("HQTable").DataBodyRange(Sheet4.ListObjects("HQTable").ListRows.Count, Sheet4.ListObjects("HQTable").ListColumns("Another Data").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Another Data").Range.Column)
        
        Sheet4.ListObjects("HQTable").DataBodyRange(Sheet4.ListObjects("HQTable").ListRows.Count, Sheet4.ListObjects("HQTable").ListColumns("Text").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Text").Range.Column)
       
        Sheet4.ListObjects("HQTable").DataBodyRange(Sheet4.ListObjects("HQTable").ListRows.Count, Sheet4.ListObjects("HQTable").ListColumns("Something").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Something").Range.Column)
       
        Sheet4.ListObjects("HQTable").DataBodyRange(Sheet4.ListObjects("HQTable").ListRows.Count, Sheet4.ListObjects("HQTable").ListColumns("Country").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Country").Range.Column)
        
        Sheet4.ListObjects("HQTable").DataBodyRange(Sheet4.ListObjects("HQTable").ListRows.Count, Sheet4.ListObjects("HQTable").ListColumns("Region").Range.Column) = _
        D1LUR
        
        Sheet4.ListObjects("HQTable").DataBodyRange(Sheet4.ListObjects("HQTable").ListRows.Count, Sheet4.ListObjects("HQTable").ListColumns("Something Else").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Something Else").Range.Column)
       
        Sheet4.ListObjects("HQTable").DataBodyRange(Sheet4.ListObjects("HQTable").ListRows.Count, Sheet4.ListObjects("HQTable").ListColumns("Number").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Number").Range.Column)
       
        Sheet4.ListObjects("HQTable").DataBodyRange(Sheet4.ListObjects("HQTable").ListRows.Count, Sheet4.ListObjects("HQTable").ListColumns("Another Note").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Another Note").Range.Column)
    
    ElseIf D1LUR = "NORTH" Then
    
        Sheet4.ListObjects("NorthTable").ListRows.Add.Range(1, Sheet4.ListObjects("NorthTable").ListColumns("Data").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Data").Range.Column)
        
        Sheet4.ListObjects("NorthTable").DataBodyRange(Sheet4.ListObjects("NorthTable").ListRows.Count, Sheet4.ListObjects("NorthTable").ListColumns("Other Data").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Other Data").Range.Column)
       
        Sheet4.ListObjects("NorthTable").DataBodyRange(Sheet4.ListObjects("NorthTable").ListRows.Count, Sheet4.ListObjects("NorthTable").ListColumns("Another Data").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Another Data").Range.Column)
        
        Sheet4.ListObjects("NorthTable").DataBodyRange(Sheet4.ListObjects("NorthTable").ListRows.Count, Sheet4.ListObjects("NorthTable").ListColumns("Text").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Text").Range.Column)
       
        Sheet4.ListObjects("NorthTable").DataBodyRange(Sheet4.ListObjects("NorthTable").ListRows.Count, Sheet4.ListObjects("NorthTable").ListColumns("Something").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Something").Range.Column)
       
        Sheet4.ListObjects("NorthTable").DataBodyRange(Sheet4.ListObjects("NorthTable").ListRows.Count, Sheet4.ListObjects("NorthTable").ListColumns("Country").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Country").Range.Column)
        
        Sheet4.ListObjects("NorthTable").DataBodyRange(Sheet4.ListObjects("NorthTable").ListRows.Count, Sheet4.ListObjects("NorthTable").ListColumns("Region").Range.Column) = _
        D1LUR
        
        Sheet4.ListObjects("NorthTable").DataBodyRange(Sheet4.ListObjects("NorthTable").ListRows.Count, Sheet4.ListObjects("NorthTable").ListColumns("Something Else").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Something Else").Range.Column)
       
        Sheet4.ListObjects("NorthTable").DataBodyRange(Sheet4.ListObjects("NorthTable").ListRows.Count, Sheet4.ListObjects("NorthTable").ListColumns("Number").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Number").Range.Column)
       
        Sheet4.ListObjects("NorthTable").DataBodyRange(Sheet4.ListObjects("NorthTable").ListRows.Count, Sheet4.ListObjects("NorthTable").ListColumns("Another Note").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Another Note").Range.Column)
  
    ElseIf D1LUR = "SOUTH" Then
    
        Sheet4.ListObjects("SouthTable").ListRows.Add.Range(1, Sheet4.ListObjects("SouthTable").ListColumns("Data").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Data").Range.Column)
        
        Sheet4.ListObjects("SouthTable").DataBodyRange(Sheet4.ListObjects("SouthTable").ListRows.Count, Sheet4.ListObjects("SouthTable").ListColumns("Other Data").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Other Data").Range.Column)
       
        Sheet4.ListObjects("SouthTable").DataBodyRange(Sheet4.ListObjects("SouthTable").ListRows.Count, Sheet4.ListObjects("SouthTable").ListColumns("Another Data").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Another Data").Range.Column)
        
        Sheet4.ListObjects("SouthTable").DataBodyRange(Sheet4.ListObjects("SouthTable").ListRows.Count, Sheet4.ListObjects("SouthTable").ListColumns("Text").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Text").Range.Column)
       
        Sheet4.ListObjects("SouthTable").DataBodyRange(Sheet4.ListObjects("SouthTable").ListRows.Count, Sheet4.ListObjects("SouthTable").ListColumns("Something").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Something").Range.Column)
       
        Sheet4.ListObjects("SouthTable").DataBodyRange(Sheet4.ListObjects("SouthTable").ListRows.Count, Sheet4.ListObjects("SouthTable").ListColumns("Country").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Country").Range.Column)
        
        Sheet4.ListObjects("SouthTable").DataBodyRange(Sheet4.ListObjects("SouthTable").ListRows.Count, Sheet4.ListObjects("SouthTable").ListColumns("Region").Range.Column) = _
        D1LUR
        
        Sheet4.ListObjects("SouthTable").DataBodyRange(Sheet4.ListObjects("SouthTable").ListRows.Count, Sheet4.ListObjects("SouthTable").ListColumns("Something Else").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Something Else").Range.Column)
       
        Sheet4.ListObjects("SouthTable").DataBodyRange(Sheet4.ListObjects("SouthTable").ListRows.Count, Sheet4.ListObjects("SouthTable").ListColumns("Number").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Number").Range.Column)
       
        Sheet4.ListObjects("SouthTable").DataBodyRange(Sheet4.ListObjects("SouthTable").ListRows.Count, Sheet4.ListObjects("SouthTable").ListColumns("Another Note").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Another Note").Range.Column)
  
    ElseIf D1LUR = "BLANK" Then
   
        Sheet4.ListObjects("BLANKTable").ListRows.Add.Range(1, Sheet4.ListObjects("BLANKTable").ListColumns("Data").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Data").Range.Column)
        
        Sheet4.ListObjects("BLANKTable").DataBodyRange(Sheet4.ListObjects("BLANKTable").ListRows.Count, Sheet4.ListObjects("BLANKTable").ListColumns("Other Data").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Other Data").Range.Column)
       
        Sheet4.ListObjects("BLANKTable").DataBodyRange(Sheet4.ListObjects("BLANKTable").ListRows.Count, Sheet4.ListObjects("BLANKTable").ListColumns("Another Data").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Another Data").Range.Column)
        
        Sheet4.ListObjects("BLANKTable").DataBodyRange(Sheet4.ListObjects("BLANKTable").ListRows.Count, Sheet4.ListObjects("BLANKTable").ListColumns("Text").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Text").Range.Column)
       
        Sheet4.ListObjects("BLANKTable").DataBodyRange(Sheet4.ListObjects("BLANKTable").ListRows.Count, Sheet4.ListObjects("BLANKTable").ListColumns("Something").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Something").Range.Column)
       
        Sheet4.ListObjects("BLANKTable").DataBodyRange(Sheet4.ListObjects("BLANKTable").ListRows.Count, Sheet4.ListObjects("BLANKTable").ListColumns("Country").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Country").Range.Column)
        
        Sheet4.ListObjects("BLANKTable").DataBodyRange(Sheet4.ListObjects("BLANKTable").ListRows.Count, Sheet4.ListObjects("BLANKTable").ListColumns("Region").Range.Column) = _
        D1LUR
        
        Sheet4.ListObjects("BLANKTable").DataBodyRange(Sheet4.ListObjects("BLANKTable").ListRows.Count, Sheet4.ListObjects("BLANKTable").ListColumns("Something Else").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Something Else").Range.Column)
       
        Sheet4.ListObjects("BLANKTable").DataBodyRange(Sheet4.ListObjects("BLANKTable").ListRows.Count, Sheet4.ListObjects("BLANKTable").ListColumns("Number").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Number").Range.Column)
       
        Sheet4.ListObjects("BLANKTable").DataBodyRange(Sheet4.ListObjects("BLANKTable").ListRows.Count, Sheet4.ListObjects("BLANKTable").ListColumns("Another Note").Range.Column) = _
        Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Another Note").Range.Column)
  
    Else
    
    MsgBox "There is no region in the Look Up Table for this country.", vbOKOnly


    End If


Next


Fixit:


If D1LUV = vbNullString Then
    Sheet2.Activate
    Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Country").Range.Column).Activate
    Correction = MsgBox("The cell in Table1 is empty, do you want to change the cell in Table1?", vbYesNo, "Empty cells are not allowed!")
Else
    Sheet2.Activate
    Sheet2.ListObjects("Table1").Range(m, Sheet2.ListObjects("Table1").ListColumns("Country").Range.Column).Activate
    Correction = MsgBox(" ' " & D1LUV & " ' " & " is not in the Look Up Table on the Admin Sheet, do you want to change the cell in Table1?", vbYesNo, "Is this a typo?")
End If


    If Correction = vbYes Then


        If D1LUV = vbNullString Then
        Empty_Cell_Userform.Show
        Else
        Typo_Userform.Show
        End If
        Err.Clear


    On Error GoTo -1
    GoTo Resume_run


Exit Sub


End If


Sheet3.Activate


Dim l As Long
Dim D2LUV As String 'D_ata 2 L_ook U_p V_alue
Dim D2LUR As String 'D_ata 2 L_ook U_p R_esult


For l = 2 To Sheet3.Cells.Find(what:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row


Resume_second_run:


D2LUV = Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Country2").Range.Column)


On Error GoTo Fixit_Again


D2LUR = WorksheetFunction.VLookup(D2LUV, Sheet1.ListObjects("LookUpTable").DataBodyRange, Sheet1.ListObjects("LookUpTable").ListColumns("Region").Range.Column, False)


On Error GoTo 0


    If D2LUR = "EAST" Then
        
        Sheet4.ListObjects("EastTable").ListRows.Add.Range(1, Sheet4.ListObjects("EastTable").ListColumns("Other Data").Range.Column) = _
        Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Other Data").Range.Column)
        
        Sheet4.ListObjects("EastTable").DataBodyRange(Sheet4.ListObjects("EastTable").ListRows.Count, Sheet4.ListObjects("EastTable").ListColumns("Another Data").Range.Column) = _
        Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Another Data").Range.Column)
        
        Sheet4.ListObjects("EastTable").DataBodyRange(Sheet4.ListObjects("EastTable").ListRows.Count, Sheet4.ListObjects("EastTable").ListColumns("Text").Range.Column) = _
        Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Text").Range.Column)
       
        Sheet4.ListObjects("EastTable").DataBodyRange(Sheet4.ListObjects("EastTable").ListRows.Count, Sheet4.ListObjects("EastTable").ListColumns("Something").Range.Column) = _
        Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Something").Range.Column)
       
        Sheet4.ListObjects("EastTable").DataBodyRange(Sheet4.ListObjects("EastTable").ListRows.Count, Sheet4.ListObjects("EastTable").ListColumns("Country").Range.Column) = _
        Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Country2").Range.Column)
        
        Sheet4.ListObjects("EastTable").DataBodyRange(Sheet4.ListObjects("EastTable").ListRows.Count, Sheet4.ListObjects("EastTable").ListColumns("Region").Range.Column) = _
        D2LUR
        
    ElseIf D2LUR = "HQ" Then
    
        Sheet4.ListObjects("HQTable").ListRows.Add.Range(1, Sheet4.ListObjects("HQTable").ListColumns("Other Data").Range.Column) = _
        Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Other Data").Range.Column)
        
        Sheet4.ListObjects("HQTable").DataBodyRange(Sheet4.ListObjects("HQTable").ListRows.Count, Sheet4.ListObjects("HQTable").ListColumns("Another Data").Range.Column) = _
        Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Another Data").Range.Column)
        
        Sheet4.ListObjects("HQTable").DataBodyRange(Sheet4.ListObjects("HQTable").ListRows.Count, Sheet4.ListObjects("HQTable").ListColumns("Text").Range.Column) = _
        Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Text").Range.Column)
       
        Sheet4.ListObjects("HQTable").DataBodyRange(Sheet4.ListObjects("HQTable").ListRows.Count, Sheet4.ListObjects("HQTable").ListColumns("Something").Range.Column) = _
        Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Something").Range.Column)
       
        Sheet4.ListObjects("HQTable").DataBodyRange(Sheet4.ListObjects("HQTable").ListRows.Count, Sheet4.ListObjects("HQTable").ListColumns("Country").Range.Column) = _
        Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Country2").Range.Column)


        Sheet4.ListObjects("HQTable").DataBodyRange(Sheet4.ListObjects("HQTable").ListRows.Count, Sheet4.ListObjects("HQTable").ListColumns("Region").Range.Column) = _
        D2LUR
        
    ElseIf D2LUR = "NORTH" Then
    
        Sheet4.ListObjects("NorthTable").ListRows.Add.Range(1, Sheet4.ListObjects("NorthTable").ListColumns("Other Data").Range.Column) = _
        Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Other Data").Range.Column)
        
        Sheet4.ListObjects("NorthTable").DataBodyRange(Sheet4.ListObjects("NorthTable").ListRows.Count, Sheet4.ListObjects("NorthTable").ListColumns("Another Data").Range.Column) = _
        Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Another Data").Range.Column)
        
        Sheet4.ListObjects("NorthTable").DataBodyRange(Sheet4.ListObjects("NorthTable").ListRows.Count, Sheet4.ListObjects("NorthTable").ListColumns("Text").Range.Column) = _
        Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Text").Range.Column)
       
        Sheet4.ListObjects("NorthTable").DataBodyRange(Sheet4.ListObjects("NorthTable").ListRows.Count, Sheet4.ListObjects("NorthTable").ListColumns("Something").Range.Column) = _
        Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Something").Range.Column)
       
        Sheet4.ListObjects("NorthTable").DataBodyRange(Sheet4.ListObjects("NorthTable").ListRows.Count, Sheet4.ListObjects("NorthTable").ListColumns("Country").Range.Column) = _
        Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Country2").Range.Column)
        
        Sheet4.ListObjects("NorthTable").DataBodyRange(Sheet4.ListObjects("NorthTable").ListRows.Count, Sheet4.ListObjects("NorthTable").ListColumns("Region").Range.Column) = _
        D2LUR
        
    ElseIf D2LUR = "SOUTH" Then
    
        Sheet4.ListObjects("SouthTable").ListRows.Add.Range(1, Sheet4.ListObjects("SouthTable").ListColumns("Other Data").Range.Column) = _
        Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Other Data").Range.Column)
        
        Sheet4.ListObjects("SouthTable").DataBodyRange(Sheet4.ListObjects("SouthTable").ListRows.Count, Sheet4.ListObjects("SouthTable").ListColumns("Another Data").Range.Column) = _
        Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Another Data").Range.Column)
        
        Sheet4.ListObjects("SouthTable").DataBodyRange(Sheet4.ListObjects("SouthTable").ListRows.Count, Sheet4.ListObjects("SouthTable").ListColumns("Text").Range.Column) = _
        Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Text").Range.Column)
       
        Sheet4.ListObjects("SouthTable").DataBodyRange(Sheet4.ListObjects("SouthTable").ListRows.Count, Sheet4.ListObjects("SouthTable").ListColumns("Something").Range.Column) = _
        Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Something").Range.Column)
       
        Sheet4.ListObjects("SouthTable").DataBodyRange(Sheet4.ListObjects("SouthTable").ListRows.Count, Sheet4.ListObjects("SouthTable").ListColumns("Country").Range.Column) = _
        Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Country2").Range.Column)
        
        Sheet4.ListObjects("SouthTable").DataBodyRange(Sheet4.ListObjects("SouthTable").ListRows.Count, Sheet4.ListObjects("SouthTable").ListColumns("Region").Range.Column) = _
        D2LUR
        
    ElseIf D2LUR = "BLANK" Then
    
        Sheet4.ListObjects("BLANKTable").ListRows.Add.Range(1, Sheet4.ListObjects("BLANKTable").ListColumns("Other Data").Range.Column) = _
        Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Other Data").Range.Column)
        
        Sheet4.ListObjects("BLANKTable").DataBodyRange(Sheet4.ListObjects("BLANKTable").ListRows.Count, Sheet4.ListObjects("BLANKTable").ListColumns("Another Data").Range.Column) = _
        Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Another Data").Range.Column)
        
        Sheet4.ListObjects("BLANKTable").DataBodyRange(Sheet4.ListObjects("BLANKTable").ListRows.Count, Sheet4.ListObjects("BLANKTable").ListColumns("Text").Range.Column) = _
        Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Text").Range.Column)
       
        Sheet4.ListObjects("BLANKTable").DataBodyRange(Sheet4.ListObjects("BLANKTable").ListRows.Count, Sheet4.ListObjects("BLANKTable").ListColumns("Something").Range.Column) = _
        Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Something").Range.Column)
       
        Sheet4.ListObjects("BLANKTable").DataBodyRange(Sheet4.ListObjects("BLANKTable").ListRows.Count, Sheet4.ListObjects("BLANKTable").ListColumns("Country").Range.Column) = _
        Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Country2").Range.Column)


        Sheet4.ListObjects("BLANKTable").DataBodyRange(Sheet4.ListObjects("BLANKTable").ListRows.Count, Sheet4.ListObjects("BLANKTable").ListColumns("Region").Range.Column) = _
        D2LUR
    
    Else
    
    MsgBox "There is no region for this country.", vbOKOnly
    
    End If
    
Next


Fixit_Again:


If D2LUV = vbNullString Then
    Sheet3.Activate
    Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Country2").Range.Column).Activate
    Correction = MsgBox("The cell in Table2 is empty, do you want to change the cell in Table2?", vbYesNo, "Empty cells are not allowed!")
Else
    Sheet3.Activate
    Sheet3.ListObjects("Table2").Range(l, Sheet3.ListObjects("Table2").ListColumns("Country2").Range.Column).Activate
    Correction = MsgBox(" ' " & D2LUV & " ' " & " is not in the Look Up Table on the Admin Sheet, do you want to change the cell in Table2?", vbYesNo, "Is this a typo?")
End If


    If Correction = vbYes Then


        If D2LUV = vbNullString Then
        Empty_Cell_Userform.Show
        Else
        Typo_Userform.Show
        End If
        Err.Clear


    On Error GoTo -1
    GoTo Resume_second_run


Exit Sub


End If


Sheet4.ListObjects("EastTable").DataBodyRange.WrapText = False
Sheet4.ListObjects("HQTable").DataBodyRange.WrapText = False
Sheet4.ListObjects("NorthTable").DataBodyRange.WrapText = False
Sheet4.ListObjects("SouthTable").DataBodyRange.WrapText = False
Sheet4.ListObjects("BLANKTable").DataBodyRange.WrapText = False


End Sub
 
Last edited by a moderator:

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
You have two lines that take you back to the beginning (in red below):
Code: 
...
Fixit:
...
[COLOR=#ff0000]    GoTo Resume_run[/COLOR]
...
Fixit_Again:
....
[COLOR=#ff0000]    GoTo Resume_second_run[/COLOR]
....
Basically what you have done is that after you finish the FOR loop you got through the FixIt section (which the way I see it should only be used in some cases) and then get to GoTo Resume_run which takes you back to the beginning.
One way to do it is have a row label (e.g. SkipFix: ) after the fixit section and a GoTo SkipFix just before the FixIt section.
Another way (preferably) is to structure the code in a way to complete it without necessarily passing through the Fixit sections:
Code: 
[B]Sub Create_Tables_Split_By_Region()
[/B]
[I]   .... all your code without the two FixIt sections ...
[/I]
   [B]Exit sub[/B]

[B]Fixit:[/B]
    ...
[COLOR=#FF0000]    GoTo Resume_run[/COLOR]

[B]Fixit_Again:[/B]
    ....
[COLOR=#FF0000]    GoTo Resume_second_run[/COLOR]
[B]End Sub[/B]
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Locked
  • Question
Change Code from New to Update
Replies
1
Views
478
Fluff
Fluff
A
  • Question
Page break issues when exporting to pdf from userform (VBA)
Replies
0
Views
707
auglocqnuk
A
L
  • Question
Loop runs into subscript out of range in the last row?
Replies
3
Views
1K
DanteAmor
DanteAmor
N
  • Question
VBA Looping a value in table range and display multiple rows
Replies
1
Views
276
DanteAmor
DanteAmor
L
  • Question
Fixing errors with userform while macro running?
Replies
1
Views
521
Lil2606
L

Forum statistics

Threads
1,214,979
Messages
6,122,560
Members
449,089
Latest member
Motoracer88

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