Excel always crashes at the very same point of the code

[freakygreeneyes]

Hi community

I've still got some serious trouble with a Excel-document I'm working on. It's a file supposed to compare offers from various building firms. Thus I wrote a button-released code to sort the offers from cheapest to most expensive. I think the code should be all right by now, but every time I run it, excel crashes (with "runtime error 9: subscript out of range", when I press "debug", the program shuts down). By step by step running the code, I figured that it always crashes on line 14, no matter whats wirtten on this line. This made me think it might be a software-problem. But I tried running it on an other computer and had the same Problems. I also tried copy-paste into another file, but it didn't solve my problems neither
As attachment you will find the file. I'm sorry it's in German. The row to be sorted on is row 43.
I'm using windows 7 and ms-office 2010 professional plus. in case that helps ;-)

Thanks a lot for your help!
Greets
Freakygreeneyes

[Norie]

The code errors here because there's no workbook called '03_Submissions-AnswertungTest.xlsm' open.

Please Login or Register  to view this content.

[freakygreeneyes]

hi Norie
Oh I really just got this myslef. I changed the name of the file i uploaded, so i could delete a few things due privacy reasons and just forgot to adapt the code. I already loaded up the corrected file where the other one was.
Thanks anyways

Greets

[Norie]

If you are trying to refer to the workbook the code is in use ThisWorkbook.

[freakygreeneyes]

Tried that bevor, doesn't change anything...
thanks

[Norie]

Are you trying to refer to the workbook the code is in?

[freakygreeneyes]

Dear Norie

Sorry for having you waiting so long for me to answer...
Yes, I'm trying to refer to the workbook the code is in. Does that explain my problem somehow?

Greets

[jolivanes]

Ran the code on my old machine with 2007 and worked OK, meaning no runtime error after deleting the

Please Login or Register  to view this content.

line

[freakygreeneyes]

Hey jolivanes

I just tried your tip. Strange, it still doesn't work on my Office 2010, but it actually does on my office for mac 2011.
Is there any reason apparent, why it doesn't work on Office 2010?
Greets

[freakygreeneyes]

Hey communitiy

I tried saving the workbook in office version 97-03. Now it works! Thats quite good, but it would be better if it did also work for Office 2010, so I'm looking forward to hear from you, if you know whats screwing my code uf in office 2010.
So far, thanks for your help!

Greets!

[jolivanes]

Hi,

I can't tell you why that would be. I don't have 2010 to try it with.
Someone with 2007 and 2010 hopefully will get you the answer.
Good luck

[JosephP]

I can't fathom the reason but it crashes when it assigns the sheet to the wsAuswertung variable. if you copy the sheet and use the copy in the code instead it seems to work ok

[Norie]

If you copy all the sheets to a new workbook the code seems to work fine after changing it to use ThisWorkbook.

Please Login or Register  to view this content.