0

I recently created this macro that works fine on my station (running Excel 2016) but crashes on other people's station (Excel 2010). How can I fix this to work for them?

        Sub MACRO1()
'
' MACRO1 Macro
'

'    
           Sheets("SHEET3").Activate
        Range("A1").AutoFilter
        Range("A2", Range("A2").End(xlDown).End(xlToRight)).ClearContents
        Sheets("SHEET4").Activate
        Range("A1").AutoFilter
        Range("A2", Range("A2").End(xlDown).End(xlToRight)).ClearContents
        If ActiveWorkbook.Sheets("SHEET1").Range("A2").Value = "" Then Exit Sub
        Sheets("SHEET1").Activate
        Cells.UnMerge
        Cells.RemoveDuplicates Columns:=Array(2, 16), _
            Header:=xlYes
        Columns("B:B").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
            Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _
            Array(1, 1), TrailingMinusNumbers:=True
        Range("A1").Select
        Sheets("SHEET2").Activate
        Cells.RemoveDuplicates Columns:=Array(2, 9), _
            Header:=xlNo
        Columns("B:B").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
            Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _
            Array(1, 1), TrailingMinusNumbers:=True
        Range("A1").Select
        Sheets("SHEET3").Activate
        Range("A2:Q1000").FormulaR1C1 = _
            "=IF('SHEET2'!R[1]C="""","""",'SHEET2'!R[1]C)"
        Range("R2:R1000").FormulaR1C1 = _
            "=IF(RC[-16]="""","""",IF(ISNA(VLOOKUP(RC[-16],'SHEET1'!C2:C16,15,FALSE)),""MISSING"",IF(TRUNC((VLOOKUP(RC[-16],'SHEET1'!C2:C16,15,FALSE)),6)<>'SHEET2'!R[1]C9,""INCORRECT"",""CORRECT"")))"
        With ActiveSheet.UsedRange
            .Value = .Value
        End With
        ActiveSheet.Range("$A$1:$R$1000").AutoFilter Field:=18, Criteria1:= _
            "=INCORRECT", Operator:=xlOr, Criteria2:="=MISSING"
        ActiveWorkbook.Worksheets("SHEET4").AutoFilter.Sort.SortFields.CLEAR
        ActiveWorkbook.Worksheets("SHEET4").AutoFilter.Sort.SortFields.Add2 _
            Key:=Range("R1:R1000"), SortOn:=xlSortOnValues, Order:=xlDescending, _
            DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("SHEET4").AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Sheets("SHEET4").Activate
        Range("A2:Q1000").FormulaR1C1 = _
            "=IF('SHEET1'!RC2="""","""",'SHEET1'!RC)"
        Range("P2:P1000").FormulaR1C1 = _
            "=IF('SHEET1'!RC2="""","""",(TRUNC('SHEET1'!RC,6)))"
        Range("R2:R1000").FormulaR1C1 = _
            "=IF(RC[-16]="""","""",IF(OR(((TODAY())<(DATE((LEFT(RC[-14],4)),(MID(RC[-14],5,2)),(RIGHT(RC[-14],2))))),(TODAY()=(DATE((LEFT(RC[-14],4)),(MID(RC[-14],5,2)),(RIGHT(RC[-14],2)))))),""CORRECT"",IF(ISNA(VLOOKUP(RC[-16],'SHEET2'!C2:C9,8,FALSE)),""MISSING"",IF(VLOOKUP(RC[-16],'SHEET2'!C2:C9,8,FALSE)<>RC[-2],""INCORRECT"",""CORRECT""))))"
        With ActiveSheet.UsedRange
            .Value = .Value
        End With
        ActiveSheet.Range("$A$1:$R$1000").AutoFilter Field:=18, Criteria1:= _
            "=INCORRECT", Operator:=xlOr, Criteria2:="=MISSING"
        ActiveWorkbook.Worksheets("SHEET3").AutoFilter.Sort.SortFields.CLEAR
        ActiveWorkbook.Worksheets("SHEET3").AutoFilter.Sort.SortFields.Add2 _
            Key:=Range("R1:R1000"), SortOn:=xlSortOnValues, Order:=xlDescending, _
            DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("SHEET3").AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End Sub

Again the Macro works fine on my station running excel 2016 but not on those running excel 2010. Would like to fix the code to have it run on both.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Jay
  • 21
  • 4
  • 1
    Does the error show which line the error occurs on? Also, it's best to [avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BruceWayne Aug 05 '19 at 14:48
  • unfortunately it doesn't give me the option to see where the error is occurring which is frustrating. It's hard to recreate since it doesn't happen on my terminal. And yes I heard that but i didn't know how else to write the code (this is my first) – Jay Aug 05 '19 at 14:51
  • 1
    Change `Sortfields.Add2` to `Sortfields.Add` – Rory Aug 05 '19 at 14:56
  • To add to what @Rory wrote, the `.Add2` method was added in 2016. – Ron Rosenfeld Aug 05 '19 at 17:35
  • Thank you @Rory and @ron! This fixed the issue. Appreciate the help folks! – Jay Aug 05 '19 at 18:13

0 Answers0