Sorting data in a named range

[ShogunPatch]

I have a workbook comprising two worksheets, "Form" and "Lists".

Many of the columns in "Forms" are restricted by validation lists defined in "Lists", e.g. one column is for currency and users select available currencies from a drop down box, available options for which are defined in a range called "CurrencyDetails" in "Lists" (which is protected). The particular range is currently quite small, as I only have a short list of currencies setup for now but is spread across two columns: one for the full currency name and one for a code (e.g. 'US Dollar' and 'USD')

Essentially, I want to create a command button which will open a form where users can create new currencies to add to the list.

Using my existing, basic knowledge of Excel, cobbling together code from recorded macros and with the added help of existing posts on forums such as this and an extremely helpful YouTube video I have so far managed to:

• Create the required "Create New Currency" button and get that button to:
  

1. Open and unlock the "Lists" sheet
2. Select the correct range by name "CurrencyDetails"
3. Add one row to it
4. Open a "NewCurrencyDetails" form
   

• Design the "NewCurrencyDetails"; the form requires two inputs (currency name & currency code) and also has "OK" and "Cancel" buttons
• Get the "Cancel" button to work and the "OK" button to start adding the details to the relevant named list, i.e. specifically to:

1. Input the values from the form into the correct two cells (the two newly created cells at the bottom of the named range)

The last step, which is where I am stumbling, is that I want the list to be reordered alphabetically based on the second column in the range (column H). I have tried recording the process of selecting the named range and reordering it and checking that code but the problem is even though I select the range by its name, the code seems to identify it in the sorting bit of the macro as "G8:H14", even though elsewhere it uses the range name.
Of course because the range is increased by one row everytime a currency is added it column/row coordinates are never up to date.

I hope the above is clear; I'm sure this is very basic programming but would appreciate any help with this last bit of this macro.

Cheers.

Patch

Execution code for the "OK" button on my form attached below.

Private Sub CmdAddCurrency_Click()

Range("CurrencyNameInsertionPoint") = Me.TxtCurrencyName
Range("CurrencyCodeInsertionPoint") = Me.TxtCurrencyCode

Me.Hide

    Application.Goto Reference:="CurrencyDetails"
    ActiveWorkbook.Worksheets("Lists").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Lists").Sort.SortFields.Add Key:=Range("G8:G14"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Lists").Sort
        .SetRange Range("G8:H14")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


End Sub

 

[Andrew Poulsom]

Can't you just sort the entire columns?

 

[ShogunPatch]

No because aside from the fact that I have headers in each column (which I could admittedly do without), I don't want the entire list in alphabetical order: I want the currencies used most often (GBP, EUR, USD) always to be listed first, and then the other less frequently used currencies listed alphabetically thereafter.
I'm assuming it must be a common requirement to select and sort a named range and that therefore there must be reasonably simple way to do it (if one knows what one is doing); but maybe not.
Any other suggestions?

 

[Andrew Poulsom]

If your range has a name you can replace G8:G14 with that name in your code.

 

[ShogunPatch]

Andrew,
thanks for the suggestion but I had assumed that before posting my original cry for help. But when I tried it I got the following error message:

Run time-error '1004':
The sort reference is not valid. Make sure that it is within the data that you want to sort and the first Sort By box isn't the same or blank.

If I then click Debug, it highlights ".Apply" (before the final "End With" and "End Sub").

FYI I literally just replaced the G8:G14 with CurrencyDetails (i.e. in quote marks) in the two places the reference appears.


Any other thoughts? Many thanks for taking the time to try and help.

 

[Andrew Poulsom]

This worked for me:

Sub Test()
    ActiveWorkbook.Worksheets("Lists").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Lists").Sort.SortFields.Add Key:=Range("CurrencyDetails"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Lists").Sort
        .SetRange Range("CurrencyDetails")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

I can't think why it's not working for you. What does the name CurrencyDetails refer to?

 

[ShogunPatch]

Hhmmm... strange. I have tried both pasting your code into the relevant section of my form code and setting it up as a separate macro in its own right and it doesn't work for me either way, reporting the same error as before.

"CurrencyDetails" refers to the part of the currency information list that I want to sort (currently G8:H14 though it increases in length by one row every time I click the Insert New Currency button).

FYI GI:H1 contains the column headers for that validation list ("CurrencyName" and "CurrencyCode"), G2:H3 are blank (that is where my button is), G5:H7 (AKA "CurrencyDetails") contain the details for the three currencies I want always at the top of the list (GBP, EUR, USD).

The range name "CurrencyCodes" includes all currencies and is the range used for validation purposes in the sheet "Form" but the separate range name "CurrencyDetails" includes the list of currencies excluding those at the top which I do not want sorted alphabetically.

I suspect I have probably made an elementary error which is causing the thing to crash but have to admit for now I'm flumoxed.

 

[Andrew Poulsom]

Please post a sample of your worksheet, giving cell references and the names of any ranges. I am confused about CurrencyDetails because you say it's G5:H7 then you say it's something else.

 

[ShogunPatch]

I'm so sorry, you're right - please ignore the AKA "CurrencyDetails" comment, not sure what I was thinking.

"CurrencyDetails" is definitely the part of the currency information list that I want to sort (currently G8:H14 though it increases in length by one row every time I click the Insert New Currency button).

Happy to post a sample of my sheet, but I'm not sure exactly what you mean by that, or how to do it so will need to look it up on the forum instructions a bit later.

 

[JenniferMurphy]

This worked for me:

Sub Test()
    ActiveWorkbook.Worksheets("Lists").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Lists").Sort.SortFields.Add Key:=Range("CurrencyDetails"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Lists").Sort
        .SetRange Range("CurrencyDetails")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

I can't think why it's not working for you. What does the name CurrencyDetails refer to?

Andrew,

I need to do something similar and I found this thread. I have posted your code in this thread with questions:

https://www.mrexcel.com/forum/excel...sort-one-table-match-another.html#post5243766

If you can, please take a look and add any comments you may have.

Thanks