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!!
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!!
Last edited: