2003 to 2007 compatibilty issue

[aadenning]

Hi,

I run a macro everyday and have been using 2003 until just recently. Our office has switched over to 2007 and now the macro is erroring out. I have tried a few things but nothing seems to work.

I'm receiving a "Run-time-error' -2147417848 (80010108)' Method 'Sort' of object 'Range' failed", and then the workbook locks up and I have to force quit.

Here's the code that debugger highlights:

         Range(Range("TableStart"), Range("TableLastCol").Offset(jobcount - 1, 0)).Sort _
         Key1:=Range("B50"), Order1:=xlAscending

 

[aadenning]

Joe4,

I stepped through with 2003 and it says "jobcount=551"

 

[Joe4]

Is it the exact same sheet?
If Excel 2007 returns 0, and Excel 2003 returns 551, then therein lies the problem. Now you just need to figure out why.
Stepping through your code line-by-line (using the F8 key) while watching what is happening and checking variable values may help with that debugging.

 

[aadenning]

Sorry I had stepped through the 2007 version after I changed some things and it is actually 551 just like the 2003.

I'm lost on this one. The only thing I can think of is the range of the "TableLastCol" might be throwing the error. I's defined in the drop down name menu as $CF$54. When I change it, it still throws a "Method out of Range" error.

 

[Joe4]

You have a lot of VBA code there, and it is heavily dependent upon your data, so I don't know if there is much more I can do for you.

Other than the advice I gave you about stepping through the code, the only other thing I would try to tell you is to break your code down into smaller chucks (i.e. "building blocks"). Try running the first section of code, and see if it works. If it does, add a little more. Keep doing this until you run into your problem. And then focus your attention there.

Note, when stepping though the code using the F8 key, if you resize your VBA code window to about 1/4 the screen size, you can watch what it is doing to your spreadsheet step-by-step. Sometimes this is helpful, as you can see where you are on your spreadsheet when the error occurs. For example, if you are in column A, and try to move to the left, you will get an error because there are no errors to the left of column A.

Hope this helps some. Godd luck!