Excel Crash Upon Running Macro

**MrPartial** · 05-12-2017, 03:50 PM

Hello guys! I'm having a problem. Literally out of nowhere, one of my macros is crashing excel (turns to not responding) upon execution. The part that is stumping me is I didn't change anything from the last time I used the code (which worked flawlessly), and my colleague CAN run the code on his computer without any problems. Did something change in my computer settings? It's not giving me a error when I attempt to run macro, however when I sent the workbook via email and tried to open it, I got "Error in loading DLL", which i've researched and troubleshot to no avail. Thanks for any ideas fellas

Sub CalculateandFormat()
With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

Sheets("4. EMPL DETAILS").Select

Dim LR As Long
LR = Range("C" & Rows.Count).End(xlUp).Row

'Filter Employees and Date
    
    Range("D1").Select
    Selection.AutoFilter
    On Error GoTo errorhandler
    ActiveWorkbook.Worksheets("4. EMPL DETAILS").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("4. EMPL DETAILS").AutoFilter.Sort.SortFields.Add _
        Key:=Range("A2:A999"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("4. EMPL DETAILS").AutoFilter.Sort.SortFields.Add _
        Key:=Range("D2:D999"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("4. EMPL DETAILS").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
errorhandler:
    Resume Next
    
    
'Insert Helper Columns
    Columns("A:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

'Insert Formulas
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Reference"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",CONCATENATE(RC[2],""-"",RC[1]))"
    
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Assignment"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",IF(RC[1]=R[-1]C[1],1+R[-1]C,1))"
    
'Drag Down Formulas
    Range("A2:B2").Select
        Selection.AutoFill Destination:=Range("A2:B" & LR)
        
'Insert More Formulas
    Range("W1").Select
    ActiveCell.FormulaR1C1 = "-"
    
    Range("X1").Select
    ActiveCell.FormulaR1C1 = "Work Days without Duplicates"
    Range("X2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUM(RC15-IF(AND(RC[-21]=R[1]C[-21],R[1]C[-17]=RC[-18]),1,0))"
    
    Range("Y1").Select
    ActiveCell.FormulaR1C1 = "Period Start Date"
    Range("Y2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(DATE(YEAR(RC[-19]),MONTH(RC[-19]),DAY(RC[-19]))<=VLOOKUP('1. Details'!R6C3,'REF-Dictionary'!R3C3:R14C6,2,FALSE),VLOOKUP('1. Details'!R6C3,'REF-Dictionary'!R3C3:R14C6,2,FALSE),DATE(YEAR(RC[-19]),MONTH(RC[-19]),DAY(RC[-19])))"
    
    Range("Z1").Select
    ActiveCell.FormulaR1C1 = "Period End Date"
    Range("Z2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(DATE(YEAR(RC[-19]),MONTH(RC[-19]),DAY(RC[-19]))>=VLOOKUP('1. Details'!R6C3,'REF-Dictionary'!R3C3:R14C6,3,FALSE),VLOOKUP('1. Details'!R6C3,'REF-Dictionary'!R3C3:R14C6,3,FALSE),DATE(YEAR(RC[-19]),MONTH(RC[-19]),DAY(RC[-19])))"
    
    Range("AA1").Select
    ActiveCell.FormulaR1C1 = "Bonus at Target"
    Range("AA2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC9<>""Active"",0,IF(ISERROR(VLOOKUP(RC10,'REF-Dictionary'!R2C10:R5C12,3,FALSE)),0,VLOOKUP(RC10,'REF-Dictionary'!R2C10:R5C12,3,FALSE)))"
    
    Range("AB1").Select
    ActiveCell.FormulaR1C1 = "Prorated Bonus at Target"
    Range("AB2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC9<>""Active"",0,(RC[-1]/VLOOKUP('1. Details'!R6C3,'REF-Dictionary'!R3C3:R14C6,4,FALSE)))*RC24"
    
    Range("AC1").Select
    ActiveCell.FormulaR1C1 = "Revenue Goal"
    Range("AC2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX('2. TARGETS'!R15C1:R28C14,MATCH(RC[-24],'2. TARGETS'!R15C1:R27C1,0),(INDEX('REF-Dictionary'!R3C2:R14C3,MATCH('1. Details'!R6C3,MonthsList,0),1)+1))"
    
    Range("AD1").Select
    ActiveCell.FormulaR1C1 = "Prorated Revenue Goal"
    Range("AD2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-20]=""CCSA"",RC[-1],(RC[-1]/VLOOKUP('1. Details'!R6C3,'REF-Dictionary'!R3C3:R14C6,4,FALSE))*RC24)"
    
    Range("AE1").Select
    ActiveCell.FormulaR1C1 = "Revenue Production"
    Range("AE2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIFS('3. RESULTS'!C6,'3. RESULTS'!C1,'4. EMPL DETAILS'!RC5,'3. RESULTS'!C2,"">=""&VLOOKUP('1. Details'!R6C3,'REF-Dictionary'!R3C3:R14C6,2,FALSE),'3. RESULTS'!C2,""<=""&VLOOKUP('1. Details'!R6C3,'REF-Dictionary'!R3C3:R14C6,3,FALSE))"
    
    Range("AF1").Select
    ActiveCell.FormulaR1C1 = "Prorrated Revenue Production"
    Range("AF2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-22]=""CCSA"",RC[-1],SUMIFS('3. RESULTS'!C6,'3. RESULTS'!C1,'4. EMPL DETAILS'!RC5,'3. RESULTS'!C2,"">=""&MAX('4. EMPL DETAILS'!RC25,RC6),'3. RESULTS'!C2,""<=""&MIN('4. EMPL DETAILS'!RC26,RC7)))"
    
    Range("AG1").Select
    ActiveCell.FormulaR1C1 = "Rev Att%"
    Range("AG2").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(RC[-1]/RC[-3],0)"
    
    Range("AH1").Select
    ActiveCell.FormulaR1C1 = "Weight"
    Range("AH2").Select
    ActiveCell.FormulaR1C1 = "='REF-Dictionary'!R8C11"
    
    Range("AI1").Select
    ActiveCell.FormulaR1C1 = "Payout %"
    Range("AI2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC9<>""Active"",0,VLOOKUP(RC33,RevenuePayout,3,TRUE))"
    
    Range("AJ1").Select
    ActiveCell.FormulaR1C1 = "Bonus"
    Range("AJ2").Select
    ActiveCell.FormulaR1C1 = "=RC[-8]*RC[-2]*RC[-1]"
    
    Range("AK1").Select
    ActiveCell.FormulaR1C1 = "Award"
    
    Range("AL1").Select
    ActiveCell.FormulaR1C1 = "Award Notes"
    
    Range("Am1").Select
    ActiveCell.FormulaR1C1 = "Award"
    
    Range("An1").Select
    ActiveCell.FormulaR1C1 = "Award Notes"
           
    Range("Ao1").Select
    ActiveCell.FormulaR1C1 = "Final Bonus"
    Range("Ao2").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-2],RC[-3])"
   
'Drag Down Formulas
    Range("X2:AM2").Select
        Selection.AutoFill Destination:=Range("X2:AM" & LR)
        
'Format Cells
    Cells.Select
    Cells.EntireColumn.AutoFit
        With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

    Columns("M:M").Select
    Selection.Style = "Currency"

    Columns("Y:Z").Select
    Selection.NumberFormat = "m/d/yyyy"
    Columns("AA:AF").Select
    Selection.Style = "Currency"
    Columns("AG:AI").Select
    Selection.Style = "Percent"
    Columns("AJ:AM").Select
    Selection.Style = "Currency"
    
    Rows("1:1").Select
    Selection.Font.Bold = True

    Columns("AK:AL").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

    
'Go to Statement
    Sheets("6. BONUS STATEMENTS").Select
    
'Insert Statement Formulas
    Range("A4").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(R2C1="""","""",CONCATENATE(R25C4,""-"",R2C1,""-"",employeename,))"
    
    Range("E12").Select
    ActiveCell.FormulaR1C1 = "=R[7]C"
    
    Range("E18").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C1,'4. EMPL DETAILS'!C1:C39,3,FALSE)"
    
    Range("E19").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C1,'4. EMPL DETAILS'!C1:C39,4,FALSE)"
    
    Range("E20").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C1,'4. EMPL DETAILS'!C1:C39,11,FALSE)"
    
    Range("E21").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C1,'4. EMPL DETAILS'!C1:C39,16,FALSE)"
    
    Range("D25").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C1,'4. EMPL DETAILS'!C1:C39,5,FALSE)"
    
    Range("E25").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC7,'4. EMPL DETAILS'!C11:C12,2,FALSE)),""POSITION NOT ELIGIBLE."",VLOOKUP(RC7,'4. EMPL DETAILS'!C11:C12,2,FALSE))"
    
    Range("G25").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C1,'4. EMPL DETAILS'!C1:C39,10,FALSE)"
    
    Range("I25").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C1,'4. EMPL DETAILS'!C1:C39,6,FALSE)"
    
    Range("J25").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C1,'4. EMPL DETAILS'!C1:C39,7,FALSE)"
    
    Range("K25").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C1,'4. EMPL DETAILS'!C1:C39,24,FALSE)"
    
    Range("M25").Select
    ActiveCell.FormulaR1C1 = _
        "=(VLOOKUP(R2C1,'4. EMPL DETAILS'!C1:C39,13,FALSE)/30)*R25C11"
    
    Range("O25").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C1,'4. EMPL DETAILS'!C1:C39,27,FALSE)"
    
    Range("I29").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C1,'4. EMPL DETAILS'!C1:C39,28,FALSE)"
    
    Range("J29").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C1,'4. EMPL DETAILS'!C1:C39,30,FALSE)"
    
    Range("K29").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C1,'4. EMPL DETAILS'!C1:C39,32,FALSE)"
    
    Range("M29").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C1,'4. EMPL DETAILS'!C1:C39,35,FALSE)"
    
    Range("O29").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C1,'4. EMPL DETAILS'!C1:C39,36,FALSE)"
    
    Range("D32").Select
    ActiveCell.FormulaR1C1 = _
       "=IF(VLOOKUP(R2C1,'4. EMPL DETAILS'!C1:C41,37,FALSE)="""","""",2)"
    
    Range("E32").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(R32C4="""","""",VLOOKUP(R2C1,'4. EMPL DETAILS'!C1:C41,38,FALSE))"
    
    Range("O32").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(R32C4="""","""",VLOOKUP(R2C1,'4. EMPL DETAILS'!C1:C41,37,FALSE))"
        
    Range("O49").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(""Assignment "",RIGHT(R[-46]C[-14],1),"" of "",COUNTIFS('4. EMPL DETAILS'!C3,'6. BONUS STATEMENTS'!R18C5))"

    Range("D33").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(VLOOKUP(R2C1,'4. EMPL DETAILS'!C1:C41,39,FALSE)="""","""",R[-1]C+1)"

    Range("E33").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(R32C4="""","""",VLOOKUP(R2C1,'4. EMPL DETAILS'!C1:C41,40,FALSE))"


    Range("O33").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(R32C4="""","""",VLOOKUP(R2C1,'4. EMPL DETAILS'!C1:C41,39,FALSE))"

    Range("O34").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"



' RefreshEmpl Macro
    ActiveWorkbook.RefreshAll
    
'Go back to starting sheet
Sheets("1. Details").Select

With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

MsgBox "Process Complete."


End Sub

Excel Crash Upon Running Macro

LinkBack

Thread Tools

Rate This Thread

Display

Threaded View

Excel Crash Upon Running Macro

Thread Information

Users Browsing this Thread

Similar Threads

The custom sort list causes my excel to crash after running and then trying to save

Macro Causes Excel to Crash (But Only Sometimes)

[SOLVED] VBA Macro causes excel to crash

App Crash when running Macros - Anyone know cause?

Excel crash - bug macro?

Prevent Excel crash when running

Excel VBA macro crash

Bookmarks

Bookmarks

Posting Permissions