Sort function crashing now in new 365, help?

[bmoremick]

Hello I am new to VBA and Excel so please excuse my ignorance on some terminology. My boss uses Excel 7 and I use 365, his code that he has used for 10 years for the Sort function is as follows.


Range("A1:A14").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes

or

Range("E1", "F" & b * 2).Sort Key1:=Range("E1"), Order1:=xlAscending, Key2:=Range("E1"), Order2:=xlAscending, Header:=xlYes

This simple one line has worked in hundreds of routines. But now with the new 365 this line of code seems to be bombing out. And when I record a macro for Sort, the code is now several lines longer. Has anyone else run into this issue? Or does someone know a simple way to to condense the new code so that it can remain as one line and not crash in 365?

Thank you.

 

[RoryA]

Welcome to the forum.

Why are you sorting by the same key twice?

 

[Yongle]

1. How is variable b created and where does it get its value?
- try inserting this line immediately above your line to determine the value of variable b

MsgBox [B]b[/B]

- MsgBox should return a positive integer
- if MsgBox does not show or it returns alpha or an error, then that is your issue

I use 365 and both lines run unchanged without any problem

Sub TestIt()
    Dim b As Integer
    b = 20
    Range("E1", "F" & b * 2).Sort Key1:=Range("E1"), Order1:=xlAscending, Key2:=Range("E1"), Order2:=xlAscending, Header:=xlYes
End Sub

Sub TestIt2()
    Range("A1:A14").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
End Sub

 

[bmoremick]

Sorry my typo, it is just an example but should read

Range("E1", "F" & b * 2).Sort Key1:=Range("E1"), Order1:=xlAscending, Key2:=Range("F1"), Order2:=xlAscending, Header:=xlYes

 

[bmoremick]

Ok let me try that

 

[bmoremick]

I took out the variable 'b' just to make this line of code simpler. This line is erroring out only on one users PC that just 2 weeks ago was loaded with the latest version of Office 365. Prior to Office365 load, this pc and 50+ others running anywhere from Excel2007 to Excel2016 to other on Office365 run without error.

 Range("E1", "F" & b * 2).Sort Key1:=Range("E1"), Order1:=xlAscending, Key2:=Range("F1"), Order2:=xlAscending, Header:=xlYes 

Another note: Every line of code that includes the above type of Sort() command errors out on this one PC's version of Office365 Excel. The different Sort() commands vary in specific uses. Meaning some have a single Sort Key, others user variable cell references while others use absolute cell references. The only common denominator is that not a single Sort() command now works on this one PC.

 

[Yongle]

I have now tested your amended code which also runs as expected with 365

Sub TestIt()
    Dim b As Integer
    b = 20
      Range("E1", "F" & b * 2).Sort Key1:=Range("E1"),  Order1:=xlAscending, Key2:=Range("F1"), Order2:=xlAscending,  Header:=xlYes
End Sub

Q Did you test the value of b as suggested in post#3? What did the message box return?

Root cause?
Although the code may be breaking down at the Sort, I suspect that the root issue may be something different
If b returned the integer expected, try these to see if VBA is looking at correct sheet and not looping through a million rows or something
MsgBox Range("E1").Parent.Name
MsgBox Range("E1", "F" & b * 2).Rows.Count

Corruption?
I have confirmed that he code runs on 365 .The fact that it crashes in several files suggests a corruption issue. And it is similar to something that happened to me.

There may be a minor corruption somewhere possibly caused by a very old Excel file that is still in use (possibly converted some time ago from xls to xlsx)
- I had a file that had been in use since 2002 which survived until 365 (it even worked fine on 2010)
- I never got to the true root cause but it caused VBA to crash not only in that file but several others too
- 365 took a few minutes to reinstall. I opened the same file, ran the VBA, same result CRASH
- 365 was reinstalled again. The file was created afresh as was the VBA - the problem disappeared and never returned

 

[RoryA]

Every line of code that includes the above type of Sort() command errors out on this one PC's version of Office365 Excel.

What is the error message?