Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

sort order issue

  Asked By: Pamela    Date: Nov 25    Category: MS Office    Views: 826
  

I'm trying to create a macro that will sort a data range based on a
set of toggles but the macro fails when I make the sort order
(xlAscending or xlDescending) an input. I think the issue is that
the string I'm using as an input comes through with quotation
marks. Any thoughts on how I might get around this problem? I
tried making the variable type a variant and an XlsortOrder but that
didn't fix the problem.


Doesn't work
-----------------
strOrder(i) = "xlDescending"

Selection.Sort Key1:=Range(strKey(1)), Order1:=strOrder(1),
Key2:=Range(strKey(2)), Order2:=strOrder(2), Key3:=Range(strKey(3)),
Order3:=strOrder(3), Header:=xlGuess, OrderCustom:=1,
MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:=xlSortNormal, DataOption3:=xlSortNormal


Does Work -
------------------
Selection.Sort Key1:=Range(strKey(1)), Order1:=xlDescending,
Key2:=Range(strKey(2)), Order2:=xlAscending, Key3:=Range(strKey(3)),
Order3:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:=xlSortNormal, DataOption3:=xlSortNormal

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Shannon Johnson     Answered On: Nov 25

xlDescending is/has a value of 2, while xlAscending has a value of 1.
Set a variable  to 1 or 2 and use it in:
... , Order1:=myVariable, ...

 
Answer #2    Answered By: Adelgiese Fischer     Answered On: Nov 25

You can use the immediate window to test the value of any variable
(after it is assigned) or constant like this:

INSIDE the Immediate window (View MENU or Ctrl+G):

? xlDescending ' You type  this
2 ' VBA responds

OR
print xlDescending
2

Also, if you leave this "print" line in the Immediate window, you can
go back any time, click *anywhere* in the line and hit ENTER to get the
current value.

There's also an F-Key (Don't have my cheat-sheet here) that will
display the value when you click (Or is it hover over) a variable.
ALSO, if the error enters the BREAK mode (Line of code highlighted in
yellow) hovering over variables/objects in that SUB will show the value.

 
Didn't find what you were looking for? Find more on sort order issue Or get search suggestion and latest updates.




Tagged: