Run Time Error - 438 in excel macro; trying to sort numbers in ascending order

Question

My excel macro is giving a

runtime error 438

It's working fine on my laptop, but when I mail the Excel to a person with Excel 2013, it's giving this error.

This is my code where there is an error. This will be used to sort the numbers to ascending order:

Sub SORT_X()
'
' SORT_X Macro
'

'
    Range("A8:A712").Select
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("E8"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:= _
        True
    Range("H8").Select
    Application.CutCopyMode = False
    Range("B8:B712").Select
    Selection.Copy
    ActiveWindow.ScrollRow = 704
    ActiveWindow.ScrollRow = 697
    ActiveWindow.ScrollRow = 545
    ActiveWindow.ScrollRow = 342
    ActiveWindow.ScrollRow = 165
    ActiveWindow.ScrollRow = 163
    ActiveWindow.ScrollRow = 151
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 1
    Range("H8").Select
    ActiveSheet.Paste
    Range("G8").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.CLEAR   
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("G8"), _    
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal   'error

I recommend to read and apply [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Also all these `ActiveWindow.ScrollRow` are not necessary to sort anything this results from scrolling while macro recording. — Pᴇʜ, Aug 06 '18 at 12:32

score 4 · Answer 1 · answered Aug 06 '18 at 12:04

4

Change this part:

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2

to this:

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add

answered Aug 06 '18 at 12:04

Rory

32,730
5
32
35

thanks a lot..for some reason Add2 works in excel 2016,but doesnt in excel 2013 – Rahul Shah Sep 28 '21 at 05:20
`Add2` shouldn't work in 2016 (certainly doesn't for me) as it was added in 365. – Rory Sep 28 '21 at 07:23

score 0 · Answer 2 · answered Aug 06 '18 at 11:45

0

The _ is an operator for splitting code to the next line. Thus remove the empty space after:

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("G8"), _

The code should be:

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("G8"), _   
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

answered Aug 06 '18 at 11:45

Vityata

42,633
8
55
100

1

its working on my excel 2016, but when i mail it to a person with excel 2013, it is giving this error – Mehuli Nanda Mazumdar Aug 06 '18 at 11:47
The space is a problem of me pasting the code incorrectly :P – Mehuli Nanda Mazumdar Aug 06 '18 at 11:49
@MehuliNandaMazumdar - did you mail the worksheet with the code or only the code? – Vityata Aug 06 '18 at 11:49
i mailed the entire macro enabled excel sheet. Its working on multiple computers(through mail), but not on the one i intend to send to. – Mehuli Nanda Mazumdar Aug 06 '18 at 11:50

Run Time Error - 438 in excel macro; trying to sort numbers in ascending order

2 Answers2