I have a set of 10 named cells that contain calculated dates. The name of each single cell ranges describes the date value. I would like to write a UDF that can be used to dynamically sort the dates and return them to the worksheet in sorted order.. I would end up with a list of 10 dates parried with their corresponding name. Changes in the model would recalculated the order of the list

I could not find a simple way to read the keyed pairs into an array or some other structure sort it and return to the worksheet (I am assuming as an array function).

Does anyone have an example of this? Or some ideas? Any help or hints would be appreciated.

Thanks

hi,

Sub ListDate()
Dim Dico As Object, c As Range
Set Dico = CreateObject("Scripting.Dictionary")
For Each nms In Names
Dico.Add Range(nms.Value), nms.Name
Next
With Sheets("Feuil2") '--- adapt the sheet name
..[A2].Resize(Dico.Count) = Application.Transpose(Dico.keys)
..[B2].Resize(Dico.Count) = Application.Transpose(Dico.Items)
..[A2].Sort Key1:=.[A2], Order1:=xlAscending, Header:=xlNo
End With
End Sub

isabelle

On Thursday, April 9, 2015 at 10:23:14 PM UTC-4, isabelle wrote:
hi,

Sub ListDate()
Dim Dico As Object, c As Range
Set Dico = CreateObject("Scripting.Dictionary")
For Each nms In Names
Dico.Add Range(nms.Value), nms.Name
Next
With Sheets("Feuil2") '--- adapt the sheet name
.[A2].Resize(Dico.Count) = Application.Transpose(Dico.keys)
.[B2].Resize(Dico.Count) = Application.Transpose(Dico.Items)
.[A2].Sort Key1:=.[A2], Order1:=xlAscending, Header:=xlNo
End With
End Sub

isabelle

this provides some clues but it looks like the data is being manually sorted on the sheet using excel functions and triggered when the sub is run.

I think what I am trying to do is t to do all the sorting in a vba UDF function that entered into excel as an array function {} and have it continually sorting as other calcs in the workbook change.

on a new sheet,
select the range A1:A10
paste this formula in range A1

=SMALL((name1;name2;name3;name4;name5;name6;name7; name8;name9;name10);ROW())
it is an array formula to insert with CTRL+Shift+Enter

isabelle

On Friday, April 10, 2015 at 1:15:45 PM UTC-4, isabelle wrote:
on a new sheet,
select the range A1:A10
paste this formula in range A1

=SMALL((name1;name2;name3;name4;name5;name6;name7; name8;name9;name10);ROW())
it is an array formula to insert with CTRL+Shift+Enter

isabelle

Thanks. That sorts the date data without any UDF but I now need the name of each of those named ranges returned in the next column based on the sort. If the dates were unique I could easily do a lookup but there may be duplicate dates.

That is why I was leaning towards the UDF that could somehow sort the date-name pairs and return a sorted array.

Function TheName(rng As Range) As String
For Each nms In Names
If rng = Range(nms.Value) Then TheName = nms.Name: Exit Function
Next
End Function

=TheName(A1)

isabelle

Le 2015-04-10 13:31, mg a écrit :

Thanks. That sorts the date data without any UDF
but I now need the name of each of those named ranges returned in the next
column based on the sort.
If the dates were unique I could easily do a lookup but there may be duplicate
dates.

That is why I was leaning towards the UDF that could somehow sort the date-name pairs and return a sorted array.

Chat box - Create and design free live chat box php embed iframe html source code for your website forum blog wap without advertisement.

sim sinh vien gia re
[size=3][color=#141823]SIM SINH VIÊN GIÁ RẺ
SIM ĐOÀN VIÊN GIÁ RẺ
SIM SINH VIÊN VIETTEL GIÁ RẺ
SIM SINH VIÊN MOBIFONE GIÁ RẺ
SIM SINH VIÊN VINAPHONE GIÁ RẺ...Xem thêm


[center]https://scontent-dfw.xx.fbcdn.net/hp...e1&oe=55A23F97[url=https://www.facebook.com/simsinhviengiare.vn][b]Sim Sinh Viên Mobi Giá Rẻ

tin nước nga Tất cả thông tin, hình ảnh, video clip về Nga tổng hợp từ tất cả các báo điện tử tại ... Cực để tạo điều kiện thuận lợi cho các công ty của nước này khai thác
tin tức nước Nga
Xem thêm: http://vietbao.ru/italia-hoa-may-man...post42745.html

Sim Sinh Viên Giá Rẻ
[size=3][color=#141823]SIM SINH VIÊN GIÁ RẺ
SIM ĐOÀN VIÊN GIÁ RẺ
SIM SINH VIÊN VIETTEL GIÁ RẺ
SIM SINH VIÊN MOBIFONE GIÁ RẺ
SIM SINH VIÊN VINAPHONE GIÁ RẺ...Xem thêm