Dynamic sort
 

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

Dynamic sort
 

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

Dynamic sort
 

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.

Dynamic sort
 

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

Dynamic sort
 

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.

Dynamic sort
 

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.

Dynamic sort
 

i do not know why you used the "Name" to do this job, you should explain most
all the work to obtain a more appropriate help.

isabelle

Le 2015-04-10 14:12, isabelle a écrit :
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.

Dynamic sort
 

another way,

Sub testAL()
Dim AL1 As Object
Dim xItem
Set AL1 = CreateObject("System.Collections.ArrayList")
For Each nms In Names
AL1.Add Range(nms.Value).Text & " - " & nms.Name
Next
AL1.Sort
Sheets("Sheet2").Range("A1") = Join(AL1.ToArray(), vbNewLine)
End Sub

you can put the result into a text file and read the result in separate cells

isabelle

Dynamic sort
 

another way without using a text file and whitout forgeting the duplicate dates.

Sub testAL()
Dim AL1 As Object
Dim xItem
Set AL1 = CreateObject("System.Collections.ArrayList")

For Each nms In Names
AL1.Add Range(nms.Value).Text & " - " & nms.Name
Next

AL1.Sort
r = AL1.ToArray()

For i = LBound(r) To UBound(r)
r1 = Split(r(i), " - ")
Sheets("Sheet2").Range("A" & i + 1) = r1(0)
Sheets("Sheet2").Range("B" & i + 1) = r1(1)
Next
End Sub

isabelle

Dynamic sort
 

Another way to 'dump' value pairs into a worksheet...

For i = LBound(r) To UBound(r)
Sheets("Sheet2").Range("A" & i + 1).Resize(1, 2) = Split(r(i), " - ")
Next

OR to 'dump' sets of multiple values...

Dim vTmp
For i = LBound(r) To UBound(r)
vTmp = Split(r(i), " - ")
Sheets("Sheet2").Range("A" & i + 1).Resize(1, UBound(vTmp) + 1) =
vTmp
Next

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

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

Dynamic sort
 

thank you! Garry, i really appreciate
isabelle

Le 2015-04-11 04:08, GS a écrit :
Another way to 'dump' value pairs into a worksheet...

For i = LBound(r) To UBound(r)
Sheets("Sheet2").Range("A" & i + 1).Resize(1, 2) = Split(r(i), " - ")
Next

OR to 'dump' sets of multiple values...

Dim vTmp
For i = LBound(r) To UBound(r)
vTmp = Split(r(i), " - ")
Sheets("Sheet2").Range("A" & i + 1).Resize(1, UBound(vTmp) + 1) = vTmp
Next

Dynamic sort
 

You're welcome, as always...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion