Macro works on 365 but not 2010

[Dazzawm]

I have a macro that works fine on my 365 and a colleagues also. Another colleague that has Office 2010 and when he runs it it comes up with Compile error: Syntax error.

Is this common? Do macros work on some office versions but not others?

Any help appreciated.

 

[Joe4]

It is not uncommon. With each version of Excel, they often introduce new functionality.
So while Excel is almost always forward compatible (things written in earlier versions of Excel almost always work on newer versions), it is not always backwards compatible (things written in newer versions of Excel often will not work on older versions on Excel).

For this reason, when creating a workbook or VBA that will be running on different versions of Excel, your best bet to have it work for all users/computers is to create it using the oldest version of Excel that someone may be using. In your case, if you create the Macro in Office 2010, it should also work for users using 365. The only thing they may need to do extra is select appropriate VBA references/libraries, if you have used any of those.

 

[Dazzawm]

Thanks I didn't write it as not capable!! Would it help if I provided the code? I believe it would have been written in a newer version.

 

[Joe4]

Thanks I didn't write it as not capable!! Would it help if I provided the code? I believe it would have been written in a newer version.

Yes, that is the problem.
To maximize its chances of working in ALL versions of Excel being used, it is best to write it in the OLDEST version of Excel someone may be using, for the reasons I mentioned above.

You can try posting your code, and we can see if there is anything obvious sticking out. However, I do not have Excel 2010 myself (I am on 365), so I don't know that I would be able to spot or confirm the difference.

One thing that may help is whoever is getting the error, if they have the option of clicking the "Debug" button when the error comes up, if they click it, it should show the line of code that is causing the issue. At least then we will be able to zero in on the offending line of code. It you could post that, that would be helpful in someone helping you to sort through this issue.

 

[Dazzawm]

I have highlighted where it errors, they are red when the error pops up these 2 lines here

ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add2 key:=rng1, _

Sub AlternatesFormatting()

    Application.ScreenUpdating = False
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "STCODE"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "DESCRIPTION"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "MANU"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "SUM"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "PRD GRP"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "ALT1"
    Range("F1").Select
    Selection.AutoFill Destination:=Range("F1:T1"), Type:=xlFillDefault
    Range("F1:T1").Select
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("A1").Select
    Cells.Select
    Range("A1").Select
    ActiveWindow.DisplayGridlines = True
    Application.CutCopyMode = False
    Cells.Select
   
    With Selection.Font
        .Name = "Arial"
        .Size = 10
    End With
    Cells.EntireColumn.AutoFit
    Range("A1").Select
   
    Dim LastRowIndex As Integer
    Dim RowIndex As Integer
    Dim UsedRng As Range
 
    Set UsedRng = ActiveSheet.UsedRange
    LastRowIndex = UsedRng.Row - 1 + UsedRng.Rows.Count
    Application.ScreenUpdating = False
 
    For RowIndex = LastRowIndex To 1 Step -1
        If Application.CountA(Rows(RowIndex)) = 0 Then
            Rows(RowIndex).Delete
        End If
    Next RowIndex
 
   
    Range("A1").Select
    Dim lr As Long
    Dim rng1 As Range
    Dim rng2 As Range
   
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Set ranges
    Set rng1 = Range("A2:A" & lr)
    Set rng2 = Range("A1:T" & lr)
   
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add2 key:=rng1, _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange rng2
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
   
    Cells.Select
    Selection.NumberFormat = "General"
    Range("A1").Select
    Application.ScreenUpdating = True
End Sub

 

[Joe4]

Try changing the "Add2" on that line of code to "Add", and see if it works on the older version of Excel (2010).

 

[Dazzawm]

I noticed the file he was using it on was too big so changed dim as integer too long then it worked. What difference would the add and add2 of made anyway?

 

[Joe4]

I noticed the file he was using it on was too big so changed dim as integer too long then it worked. What difference would the add and add2 of made anyway?

I believe that older versions of Excel used "Add" and not "Add2".
So if you try to use "Add2" on an older version, you probably would get an error (for all the reasons I explained in my first post).

One easy way to check would be to record a Macro on the older version where you sort data, and see which command it uses.

 

[RoryA]

Add2 will not work in 2010.

 

[Dazzawm]

I believe that older versions of Excel used "Add" and not "Add2".
So if you try to use "Add2" on an older version, you probably would get an error (for all the reasons I explained in my first post).

One easy way to check would be to record a Macro on the older version where you sort data, and see which command it uses.

I think I changed to Add anyway which threw up another overflow error which made me think about the integer and long.