xldescending and xlascending

[blue333]

Hey

I am creating a macro which sorts three columns of my choosing in either ascending or descending order. I would like to be able to specify the order (ascending or descending) from a drop down list. I have done this part with named ranges which store the choices. However, the macro for some reason does not pick up the values. Sheet1.Range("primary_key_order").Value is producing the value it should (xldescending or xlascending) because i created a msgbox to see the value. The code will work if i use the default values. I am using Excel 2007 and Vista. Below is my code:

'DOES NOT WORK
Sheet3.Sort.SortFields.Add Key:=Range(PrimaryKeyRange), SortOn:=xlSortOnValues, Order:=Sheet1.Range("primary_key_order").Value, DataOption:=xlSortNormal

'WORKS
Sheet3.Sort.SortFields.Add Key:=Range(PrimaryKeyRange), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

'REST OF CODE...

A workaround is to use multiple if statements or case statements but these approaches get a bit messy.


Any suggestions for why my approach doesn't work?

Thanks!!

 

[Greg Truby]

What exactly is in your range primary_sort_order? Is it a 1 or a 2 or is it "xlAscending" or "xlDescending"?

I ask because xlAscending is a constant with a value of 1.

 

[blue333]

What exactly is in your range primary_sort_order? Is it a 1 or a 2 or is it "xlAscending" or "xlDescending"?

I ask because xlAscending is a constant with a value of 1.

it is actually "xlAscending" or "xlDescending" strings. so if xlAscending is 1 then what is xlDescending?

 

[Greg Truby]

Go to the VBE, hit the F2 key. Type "xldescending" into the search box. Click on the contant in the members pane. Look at the bottom of the object browser. There will be a pane with the value. And that's how you suss out contants' values. :wink:

 

[blue333]

thanks for teaching a man how to fish and not giving the fish...much appreciated!! now i know!!!!!!

 

[blue333]

thanks for teaching a man how to fish and not giving the fish...much appreciated!! now i know!!!!!!

 

[blue333]

thanks!!! it works now!!!!

 

[Domaldhino]

Thanks a lot, taught me to fish also!