Excel Crashes after running VBA Code

Certified

Board Regular
Joined
Jan 24, 2012
Messages
181
Hi,

My code crashes when the final "End Sub" runs. I tried everything I can think of including creating a new excel workbook and copying the code into that workbook. I also checked this site, but I can't find a situation that matches mine. Any thoughts? The code crashes at the end of the "Main" sub.

VBA Code:
Option Explicit

Dim wsData As Worksheet

Sub Main()

    Dim arrTrading() As Variant
    
    Dim arrCenter() As Variant
    
    Dim arrCategory() As Variant
    
    Dim arrCountry() As Variant
    
    Dim lastRow As Integer
    
     TurnOffFunctionality

    Set wsData = Sheets("State Package Data")
    
    lastRow = getLastRowByEndUp(wsData, 1)
    
    wsData.Range("M2:M" & lastRow).Clear
    
    wsData.Range("n2:n" & lastRow).Clear
    
    wsData.Range("o2:o" & lastRow).Clear
    
    ReadDataFromCloseFile arrTrading, arrCenter, arrCategory, arrCountry
    
    lookup arrCountry, lastRow, "j", 20, "n", 8, "country"
    
    lookup arrCategory, lastRow, "f", 1, "m", 3, "category"
    
    lookup arrTrading, lastRow, "j", 1, "o", 3, "trading partner"
         
    TurnOnFunctionality

End Sub
Sub lookup(arr As Variant, lastRow As Integer, lookupCol As String, matchCol As Integer, postCol As String, returnCol As Integer, name As String)

    Dim i As Integer
    
    Dim x As Integer
    
    Dim lookupValue As String
    
    Dim matchValue As String

        
    For i = 2 To lastRow
    
        lookupValue = wsData.Cells(i, lookupCol)
        
        For x = 2 To UBound(arr)
        
            matchValue = arr(x, matchCol)
        
            If lookupValue = matchValue Then
                               
                wsData.Cells(i, postCol) = arr(x, returnCol)
                
                Exit For
            
            End If
        
        Next x
    
    Next i

    Debug.Print name
    
End Sub
Sub createArrays(arrTrading As Variant, arrCenter As Variant, arrCategory As Variant, arrCountry As Variant)

    Sheets("Mapping").Activate
    
    arrCategory = Range("g1").CurrentRegion
    
    
    arrCenter = Range("k1").CurrentRegion
    
    
    arrTrading = Range("n1").CurrentRegion
    
    
    Sheets("BPC Consol Ownership").Activate
    
    arrCountry = Range("a1").CurrentRegion

End Sub

Sub ReadDataFromCloseFile(arrTrading As Variant, arrCenter As Variant, arrCategory As Variant, arrCountry As Variant)

    On Error GoTo ErrHandler
    
    Application.ScreenUpdating = False
    
    Dim src As Workbook
  
    Set src = Workbooks.Open("C:\Users\fredr\Downloads\taxpackageMapping.xlsx", True, True)
    
    createArrays arrTrading, arrCenter, arrCategory, arrCountry
    
    src.Close False
    
    Set src = Nothing
    
ErrHandler:
    Application.EnableEvents = True
    
    Application.ScreenUpdating = True
End Sub
Public Sub TurnOffFunctionality()

    Application.Calculation = xlCalculationManual
    
    Application.DisplayStatusBar = False
    
    Application.EnableEvents = False
    
    Application.ScreenUpdating = False
    
End Sub
Public Sub TurnOnFunctionality()

    Application.Calculation = xlCalculationAutomatic
    
    Application.DisplayStatusBar = True
    
    Application.EnableEvents = True
    
    Application.ScreenUpdating = True
    
End Sub
Function getLastRowByEndUp(ws As Worksheet, col As Integer)

    Dim lastRow As Integer
    
    lastRow = ws.Cells(Rows.Count, col).End(xlUp).Row
    
    getLastRowByEndUp = lastRow
    
End Function
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Watch MrExcel Video

Forum statistics

Threads
1,129,805
Messages
5,638,468
Members
417,026
Latest member
UDK

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
Top