I have written a VBA module to Sort Filtered Unique data in a specific location. The problem is, I do the same code four times.
Is there a more efficient way to use the same sorting method without having to do the same code four times?
I have supplied a copy of my code for all to review:
Please Login or Register to view this content.
Perhaps.
Please Login or Register to view this content.
If posting code please use code tags, see here.
Thanks Norie. I have a question about your array code example.
If you look at the original code I supplied there are two named ranges per sort code:
Sort 1: "SgDV_TestGrp", "SgSort_TestGrp"
Sort 2: "SgDV_Product", "SgSort_Product"
Sort 3: "SgDV_Machine", "SgSort_Machine"
Sort 4: "SgDV_SampleType", "SgSort_SampleType"
How would I handle this? Your example only shows the SGDV named ranges.
Oops did't notice that.
Please Login or Register to view this content.
OK. Obviously I'm doing something wrong, I just don't know what it is. I modified the code but it still doesn't work. I get a "Sort Method of Range class Failed" error.
Here is my modified code:
Please Login or Register to view this content.
Why do you have StartMachine in the array?
Shouldn't it be Machine?
Your right. My mistake. But even after I correct that type, I still get the "Sort Method of Range class Failed".
As far as I can see all the names are right, has anything else been changed?
No. Nothing.
Can you upload an example workbook?
Click on GO ADVANCED and use the paperclip icon to open the upload window.
OK. Here is the file.
Sorry but I'm at a loss here.
I tried a few variations, for example the below code, but still same error.
Please Login or Register to view this content.
How would handle the arrSort variant? Would you use a "For j, Next j" approach? "i" is incremented for arrRanges, but arrSort is not incremented. What are your thoughts?
The arrays have the same no of elements so you can use i for both of them.
When i is 0 arrRanges(0) will return "SgDV_TestGrp" and arrSort(0) will return "SgSort_TestGrp".
Thank you. I have never used arrays before. I tried searching the web for any explanation on use of arrays and couldn't find anything useful. Your description helps tremendously.
As for my problem, do you have any ideas?
The problem is that the Orientation argument is mispelled:
Should be xlTopToBottomPlease Login or Register to view this content.
Alternatively, since it is an optional argument and that is its default value anyway, you could just delete that argument (and the other optional arguments that are set to their default values) to correct the issue also:
Please Login or Register to view this content.
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
So that means the code I posted in #4 would work, if the error with the array was fixed.
Indeed it would!
Perhaps you could do it this way - you could play with the strings for the setups:
Please Login or Register to view this content.
Last edited by xladept; 07-26-2013 at 03:31 PM. Reason: tidy up
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
Figured out what the problem was. Look at posting #12 on the last line, the word "Bottom" is misspelled as "Botom". Once this was corrected everything worked.
Sorry, posted this without realizing that this was pointed out by tigeravatar in posting #16.
Thanks to everyone who contributed to this thread. I learned alot.
Last edited by rmoreno; 07-26-2013 at 06:35 PM.
If this is solved then please mark it solved, and spread some rep about.
Don't know how to mark as SOLVED, but this thread is SOLVED.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks