Runtime error 438

[wpryan]

Hi All, I have some code that is working fine on my computer (Office 365) but not on one of my colleague's, (Office 2016). I get a runtime error 438 on this code:

Sub RotMotPositionActual()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    Dim lRow_shRotMotPosition As Long: lRow_shRotMotPosition = shRotMotPosition.Range("A" & Rows.Count).End(xlUp).Row + 1
    shRotMotPosition.Range("A2:J" & lRow_shRotMotPosition).ClearContents
    
    shLookup.Range("RotatorFullRange").Copy shRotMotPosition.Range("A2,C2,E2,G2,I2") 'set all rotator positions in shRotMotPosition
    
    'copy Park data to shRotMotPosition
    shRotMotPosition.Range("B2:B722").FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC[-1],ImportData!R2C2:R39C3,2,FALSE)),"""",VLOOKUP(RC[-1],ImportData!R2C2:R39C3,2,FALSE))"
    shRotMotPosition.Range("B2:B722").Value = shRotMotPosition.Range("B2:B722").Value
    shRotMotPosition.Range("A2") = Round(shDataImport.Range("B40"), 0)
    shRotMotPosition.Range("A3:A722").FormulaR1C1 = "=IF(R[-1]C+1=720,1,R[-1]C+1)"
    shRotMotPosition.Range("A3:A722").Value = shRotMotPosition.Range("A3:A722").Value
    shRotMotPosition.Range("A2:A722").NumberFormat = "0"

    shRotMotPosition.Sort.SortFields.Clear
    shRotMotPosition.Sort.SortFields.Add2 _
        Key:=Range("A1:A721"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
...etc...

The error is in the last two lines. shRotMotPosition is the code name of the worksheet.
...any ideas?

 

[Joe4]

Sometimes arguments for functions change slightly in new versions.

What if you change the "Add2" in this line:

    shRotMotPosition.Sort.SortFields.Add2 _
        Key:=Range("A1:A721"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal

to "Add"?

Does it work then?

 

[wpryan]

Sometimes arguments for functions change slightly in new versions.

What if you change the "Add2" in this line:

    shRotMotPosition.Sort.SortFields.Add2 _
        Key:=Range("A1:A721"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal

to "Add"?

Does it work then?

It seems to be working. ...strange, the .Add2 line came straight from the Macro recorder. Thanks for your help!

 

[Joe4]

It seems to be working. ...strange, the .Add2 line came straight from the Macro recorder. Thanks for your help!

Yes, that is what I meant. Newer versions of Excel sometimes add a "2" on to the end of some arguments, to allow for expanded functionality (new arguments, etc).
If it is new value that was not available in the older version, it will cause errors.

A good rule of thumb when creating Macros/VBA code to be run on different versions of Excel is that if you write/record it on the computer using the OLDEST version of Excel, it should then work for everybody (as newer versions of Excel can almost always run older code, but older versions of Excel cannot necessarily run code created on newer versions).