macros keep failing in excel?

Question

When i go to push the button to activate my macro, it doesnt work and say i get error 1004. asks me if i want to End Debug or cancel.

here is my macro:

"Sub THE_SORT2()
'
' THE_SORT2 Macro
' Macro recorded 2007-10-24 by Paul Demchuk
'

'
Selection.SORT Key1:=Range("F3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ActiveWorkbook.Save
End Sub"

can you see whats wrong with it? it worked like 10 seconds ago, now it mystically stopped working.

Cheers

i dont know how to debug >.> i click debug and it highlights the code part in yellow with an arrow and when i push F8 it just brings up the error message again

Answer 1

Normally, the debugger will highlight the line that it is having trouble with in the code.

From the looks of it, I'd say this is probably some code that you recorded with the Macro Recorder. There are a couple potential problems with your code.

The Macro Recorder hard codes exactly what you did. By that I mean it HAS to sort by range F3. And that could create some problems in and of itself, especially if F3 isn't at the top of the selected range.

Then you also have the problem of it possibly erroring out if the user doesn't select a range. You need to probably avoid using the Selection portion and use the UsedRange or CurrentRegion propert to identify the area. Or you could use a named range.

You either need to write a better sorting macro, which I could possibly help you do if I knew more about what you are trying to sort. Or you need to make sure you have the data always selected and that the top row of the selected range to be row 3 before you ever run the macro. If not, your macro may error out. Row 3 is considered your header row or sorting row depending on whether or not you want row3 to be sort along with the rest of the selected data. According to your macro, Excel is guessing whether your selection has a header or not (Header: = xlGuess) It really should know whether you have a header or not. By giving a definite yes or no in the macro that it does or doesn't have a header.

Here's what I did for one I just tried out:

ActiveSheet.UsedRange.Sort _
Key1:=Range("Material"), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal

Notice that in mine I have a header row. The header row is the row about what is to be sorted. I named the cell that I sort by. That way if it ever moves it still references it. Like for instance if i ever insert a column. The used range may not work for yours though if you are starting your sort on row three and you have data in rows one and two.

Answer 2

Run it and debug it. Then use F8 (I think or F5) and run through it step by step, find the line that is giving you the problem.