Macro loops itself?

[chandler]

I'm running the following macro that selects a range and sorts it z->a. Then, the macro will do some find and delete stuff. However, the macro gets to the sort phase and then loops back up to the beginning instead of continuing. I used the macro recorded insted of typing in the code. What's the problem?

Sub temp()

ActiveWindow.ScrollRow = 1
Range("D9&quot.Select
Selection.Sort Key1:=Range("D9&quot, Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,

' This is where the code breaks and then wants to loop back to the first line. The macro will not continue to the code below

Orientation:=xlTopToBottom
Columns("B:B&quot.Select
Range("B8&quot.Activate
Selection.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(-1, -1).Range("A1&quot.Select
Range(Selection, Selection.End(xlUp)).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "zip"
Range("A1&quot.Select
Selection.CurrentRegion.Select
ActiveWorkbook.Names.Add Name:="database", RefersToR1C1:= _
"=Sheet1!R1C1:R112C1"
ActiveWorkbook.SaveAs Filename:="D:\pcode\z.dbf", FileFormat:=xlDBF4, _
CreateBackup:=False
End Sub
Chandler
I ran over my dogma with karma!

 

[Zathras]

Looks like a missing continuation flag (underscore) causing

Orientation:=xlTopToBottom

to look like the beginning of a new statement.

 

[chandler]

doh.. I made copy/paste mistake. The miscue happens after the orientation line. My post should have read:

Sub temp()

ActiveWindow.ScrollRow = 1
Range("D9&quot.Select
Selection.Sort Key1:=Range("D9&quot, Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

' This is where the code breaks and then wants to loop back to the first line. The macro will not continue to the code below

Columns("B:B&quot.Select
Range("B8&quot.Activate
Selection.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(-1, -1).Range("A1&quot.Select
Range(Selection, Selection.End(xlUp)).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "zip"
Range("A1&quot.Select
Selection.CurrentRegion.Select
ActiveWorkbook.Names.Add Name:="database", RefersToR1C1:= _
"=Sheet1!R1C1:R112C1"
ActiveWorkbook.SaveAs Filename:="D:\pcode\z.dbf", FileFormat:=xlDBF4, _
CreateBackup:=False
End Sub


Chandler
I ran over my dogma with karma!

 

[Zathras]

When I paste the code from your post into a new module, the lines

Selection.Sort Key1:=Range("D9&quot, Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom


initally show up red (invalid sytax). When I change it to

Selection.Sort Key1:=Range("D9&quot, Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom


it runs ok for me (up until the SaveAs since I don't have a pcode directory on my d: drive). Perhaps it is a data problem. Can you provide more information?

 

[chandler]

zathras, I couldn't see a difference? Either way, I pasted your code and the result was the same. Everything below the sort procedure is irrelevent because the macro never gets to those steps. I don't know if it's a data problem, because if I manually do the steps in Excel, everything works just fine. The macro either isn't recording the steps correctly, or isn't playing them back correctly. I'm stumped. Thanks for the help!
Chandler
I ran over my dogma with karma!

 

[Zathras]

It would help to know what you have in the worksheet in the vicinity of cell D9 since you are allowing Excel to select the sort range by itself. What is in row 8? what is in columns C,D,E,...

When you describe the problem as "wants to loop back to the first line." can you be a little more specific. Do you get any error message? What happens when you step thru the macro line by line (F8 key)?

Not knowing what your data looks like, I simply put a small data table in Cells D9:F12 being careful to leave columns C and G blank as well as rows 8 and 13. Macro runs fine for me. (Excel 97).

 

[chandler]

zathras, can I send you a file? Chandler
I ran over my dogma with karma!

 

[chandler]

oops, wrong button... meant to also add the following:
There are no error messages. When I step through the macro (F8), it will do the sort just fine. The data range is A9-D750. In the VBA module window, the sort routine gets highlighted and then processed. However, on the next F8, the sort routine is unhighlighted, but the curser doesn't drop to the next line. The F8 after that sends the cursor to the top of the macro again, hence the loop. Chandler
I ran over my dogma with karma!

 

[Zathras]

I'm sorry, I don't publish my E-mail address as a matter of policy. Perhaps it's time for one of the experts to step in and help out.

I did try one more time based on the knowledge that the data is in A9750. This time, the macro stopped on the line:

ActiveCell.Offset(-1, -1).Range("A1&quot.Select

Not sure why. But the shape of the data does really matter. What do you have in the first eight rows?

One more thing, I hadn't noticed before - in the previous statement you are using ".FindNext" without a preceding ".Find" - this may or may not be contributing to the problem.

 

[kylua]

Do you have defined ranges on the sheet? At the moment, the code just sorts one cell with mine.
Try replacing:

Range("D9&quot.Select
Selection.Sort Key1:=Range("D9&quot, Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,Orientation:=xlTopToBottom

with

Range("A9750&quot.Sort Key1:=Range("D9&quot, Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,Orientation:=xlTopToBottom

As I would guess, and it is a guess, that it is getting confused with what it should it be sorting.

 

[chandler]

Thanks for the help everyone. Actually, the sort works just fine. And I don't use Range("A9750&quot because the data range varies. What doesn't make sense is that the macro doesn't continue AFTER the sort. I've tried using different sized tables, putting the sort procedure at the end, the start, etc. and the result is always the same. Everything else in the macro works just fine as well. It just won't continue after the sort. Chandler
I ran over my dogma with karma!

 

[Zathras]

I'm really intrigued. If you post your e-mail address, I will send you a short note to which you would be able to reply and attach your file.

 

[xlbo]

Zathras - don't mean to step on your toes but I'd be willing to take a look as I'm well confused by this
2 heads etc etc
Geoff.Barraclough@Punchpubs.co.uk Rgds
~Geoff~

 

[Zathras]

Not a problem, Geoff. I'll send you an e-mail. If chandler follows through and sends it to you, perhaps you could forward it to me as well.

Agree, two heads are better than one.

Regards,
"Zathras"

 

[chandler]

jc_bad28@yahoo.com

Thanks a lot, you too. I'm still stumped. Right now, I'm just using 2 macros instead of 1. Not too much of a problem, but it makes a bit confusing when training the new employees. Right now, I'm thinking there is a problem with my Excel settings somehow. Maybe when I deleted MS Visual Studio, it took some key Excel components with it. Chandler
I ran over my dogma with karma!