Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a macro to sort a complex dynamic named reference
Hi,
I'm having trouble creating a macro that will sort a named reference. I have data in Columns A-D that will constantly be adding rows at the bottom. I have 4 header rows at the top so the data does not start until A5. I have created a named reference called "Totals" which looks like this: "=OFFSET(Totals!$A$5,0,0,COUNTA(Totals!$A:$A)-3,4)" I then tried the code below to sort it but am getting an error: "The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By blow isn't the same or blank." Application.Goto Reference:="Totals" Selection.Sort Key1:=Range("A5"), Order1:=xlAscending, Key2:=Range("C5") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom I've tried searching but so far haven't found any solutions for using named references instead of defined blocks of cells for this problem. Any help would be appreciated! Thank you, -Dan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a macro to sort a complex dynamic named reference
Here is how I would do it...
Dim rngToSort As Range Set rngToSort = ThisWorkbook.Names("Totals").RefersToRange With rngToSort .Sort Key1:=.Parent.Range("A5"), order1:=xlAscending, _ Key2:=.Parent.Range("C5"), order1:=xlAscending, _ Header:=xlNo End With -- HTH... Jim Thomlinson "Dan" wrote: Hi, I'm having trouble creating a macro that will sort a named reference. I have data in Columns A-D that will constantly be adding rows at the bottom. I have 4 header rows at the top so the data does not start until A5. I have created a named reference called "Totals" which looks like this: "=OFFSET(Totals!$A$5,0,0,COUNTA(Totals!$A:$A)-3,4)" I then tried the code below to sort it but am getting an error: "The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By blow isn't the same or blank." Application.Goto Reference:="Totals" Selection.Sort Key1:=Range("A5"), Order1:=xlAscending, Key2:=Range("C5") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom I've tried searching but so far haven't found any solutions for using named references instead of defined blocks of cells for this problem. Any help would be appreciated! Thank you, -Dan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a macro to sort a complex dynamic named reference
Hi Jim,
Unfortunately I still get a runtime 1004 - "Application-defined or object-defined error". My code looks like this: Sub SortTotals() Dim rngToSort As Range Set rngToSort = ThisWorkbook.Names("Totals").RefersToRange With rngToSort ..Sort Key1:=.Parent.Range("A5"), order1:=xlAscending, _ Key2:=.Parent.Range("C5"), order1:=xlAscending, _ Header:=xlNo End With End Sub Any ideas on what might be causing this? -Dan "Jim Thomlinson" wrote: Here is how I would do it... Dim rngToSort As Range Set rngToSort = ThisWorkbook.Names("Totals").RefersToRange With rngToSort .Sort Key1:=.Parent.Range("A5"), order1:=xlAscending, _ Key2:=.Parent.Range("C5"), order1:=xlAscending, _ Header:=xlNo End With -- HTH... Jim Thomlinson "Dan" wrote: Hi, I'm having trouble creating a macro that will sort a named reference. I have data in Columns A-D that will constantly be adding rows at the bottom. I have 4 header rows at the top so the data does not start until A5. I have created a named reference called "Totals" which looks like this: "=OFFSET(Totals!$A$5,0,0,COUNTA(Totals!$A:$A)-3,4)" I then tried the code below to sort it but am getting an error: "The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By blow isn't the same or blank." Application.Goto Reference:="Totals" Selection.Sort Key1:=Range("A5"), Order1:=xlAscending, Key2:=Range("C5") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom I've tried searching but so far haven't found any solutions for using named references instead of defined blocks of cells for this problem. Any help would be appreciated! Thank you, -Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating dynamic ranges which are named. | Excel Worksheet Functions | |||
Dynamic named range reference from external workbook | Excel Discussion (Misc queries) | |||
Macro defining dynamic named ranges | Excel Programming | |||
Creating a Dynamic Named Range Using Sheet Name and Column Header | Excel Programming | |||
How to dynamically reference a dynamic named range | Excel Worksheet Functions |