Sort Problem

[boylejob]

I currently have the code listed below

Please Login or Register  to view this content.

Thanks to the help of this list I am getting away from the activate and select statements, but I have been unsuccessful at getting this one rewritten. Using the set statement below how would I do the sort listed in the code above.

Please Login or Register  to view this content.

Thanks for the help!

Jeff

[mudraker]

Jeff

Just merge the worksheet variable, column range & the sort commands all into 1 continous command

Please Login or Register  to view this content.

[boylejob]

mudraker,

I have tried what you have listed, but it keeps throwing me into my error handler. I get Error #1004.

Jeff

[mudraker]

Have you declared the wsCallList variable?
Double check spelling of workbook & worksheet names

Please Login or Register  to view this content.

If this does not fix the problem can you post a copy of your workbook or at least a full copy of the macro

[boylejob]

Every appears to be spelled correctly and I have declared wsCallList as a Worksheet. Below is the code I am using in the subroutine.

Please Login or Register  to view this content.

Thanks for the help!

Jeff

[mudraker]

I think I got it this time

In the previous code range("a2") refered to the activesheet which I beleive was not wsCallList.
With the sortkey range being outside the sort range you will get an error.

This should fix it

Please Login or Register  to view this content.

[mudraker]

A couple of suggestions to improve your macro writting skills & the efficiency of the macro.

Note :- These suggestions May not be suitable as I do not have knowlwdge of the overall project and they will have a very small impact on your macro. (That small you will not even notice the difference).

Unless you have a need for variables to be used by other macros on the same module sheet all variables should be declared within the macro.
The way you have declared them causes Excel to reserve memory for them even after the macro has stopped running. By declaring them within the macro the memory is released when the macro stops running.

It is good to see you setting variables to nothing at the end of the macro is also good for releasing memory.

Please Login or Register  to view this content.

This part of the code

Please Login or Register  to view this content.

can be replaced by

Please Login or Register  to view this content.

If an item is going to be refered to only once then their is usually no advantage in setting it to a variable 1st

In the following code you set a variable with the result of a test condition when you could directly test the condition as part of the If statement

By combing 2 lines of code into 1 you can do away with the bwbopen variable
Replace

Please Login or Register  to view this content.

with & delete variable

Please Login or Register  to view this content.

Moving

Please Login or Register  to view this content.

Into the If statement does away with the need for a Goto command

Please Login or Register  to view this content.

If you wish to adopt these chages this is the total code for my susggestions

Please Login or Register  to view this content.

[boylejob]

I cannot begin to tell you how much I appreciate you taking a look at my code. I took two college classes in Visual Basic back in 1998 and started using VBA in 2000. I have never programmed for a living, but have been programming out of necessity, to make my job easier, since the mid 80's. Everything I know has been self taught and there are simply things I don't understand out of the books I have read.

I joined this list on 02/22/07 and it is amazing how much I have already learned by browsing the list and the few questions I have asked. There are so many things that I simply have not known how to use that makes my code run so much quicker. This is a TREMENDOUS resource!

By the way the last thing you sent on the sort problem solved it and it works great.

Thanks again for taking the time to look at my code!

Jeff

[mudraker]

Glad to hear the sort problem is resolved.

Like you I learnt out of a book to make my job easier.

And I am still learning.

I find assisting others to solve there problem & reading how others have solved problems on this site has also greatly improved my knowledge.