Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort an array
Hi,
I have a following Function and the sort does not appear to be working :( . Any help is greatly appreciated. ******************* Function interpolate(kind As String, x As Long, inputx As Range, inputy As Range) Dim n As Integer, i As Integer, j As Integer, index As Integer n = inputx.Cells.Count ' I.Combine Xs in Ys into one range and sort it Dim rngXY As Range Set rngXY = Union(inputx, inputy) If rngXY.Rows.Count < rngXY.Columns.Count Then rngXY.Sort Key1:=rngXY.Cells(1, 1), Order1:=xlAscending, Orientation:=xlLeftToRight Else rngXY.Sort Key1:=rngXY.Cells(1, 1), Order1:=xlAscending End If 'More code End Function ******************* -- ______ Regards, Greg |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort an array
Are you calling the function from a worksheet?
UDF's cannot change the workbook in any way: they can only return values. Tim "Greg" wrote in message ... Hi, I have a following Function and the sort does not appear to be working :( . Any help is greatly appreciated. ******************* Function interpolate(kind As String, x As Long, inputx As Range, inputy As Range) Dim n As Integer, i As Integer, j As Integer, index As Integer n = inputx.Cells.Count ' I.Combine Xs in Ys into one range and sort it Dim rngXY As Range Set rngXY = Union(inputx, inputy) If rngXY.Rows.Count < rngXY.Columns.Count Then rngXY.Sort Key1:=rngXY.Cells(1, 1), Order1:=xlAscending, Orientation:=xlLeftToRight Else rngXY.Sort Key1:=rngXY.Cells(1, 1), Order1:=xlAscending End If 'More code End Function ******************* -- ______ Regards, Greg |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort an array
I don't know what you input ranges look like, but perhaps what you want is
Try changing Set rngXY = Union(inputx, inputy) to Set rngXY = InputX.Parent.Range(inputx,inputY) -- Regards, Tom Ogilvy "Greg" wrote in message ... Hi, I have a following Function and the sort does not appear to be working :( . Any help is greatly appreciated. ******************* Function interpolate(kind As String, x As Long, inputx As Range, inputy As Range) Dim n As Integer, i As Integer, j As Integer, index As Integer n = inputx.Cells.Count ' I.Combine Xs in Ys into one range and sort it Dim rngXY As Range Set rngXY = Union(inputx, inputy) If rngXY.Rows.Count < rngXY.Columns.Count Then rngXY.Sort Key1:=rngXY.Cells(1, 1), Order1:=xlAscending, Orientation:=xlLeftToRight Else rngXY.Sort Key1:=rngXY.Cells(1, 1), Order1:=xlAscending End If 'More code End Function ******************* -- ______ Regards, Greg |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort an array
Yes, I am calling it from a worksheet. For ex,
=interpolate("linear",value,C3:K3,C4:K4) But I thought I could work with a range inside the VBA by creating an "independent" range rngXY. Apparently, I need to create an array and work with it? How would I sort an array then? Thanks -- ______ Regards, Greg "Tim Williams" wrote: Are you calling the function from a worksheet? UDF's cannot change the workbook in any way: they can only return values. Tim "Greg" wrote in message ... Hi, I have a following Function and the sort does not appear to be working :( . Any help is greatly appreciated. ******************* Function interpolate(kind As String, x As Long, inputx As Range, inputy As Range) Dim n As Integer, i As Integer, j As Integer, index As Integer n = inputx.Cells.Count ' I.Combine Xs in Ys into one range and sort it Dim rngXY As Range Set rngXY = Union(inputx, inputy) If rngXY.Rows.Count < rngXY.Columns.Count Then rngXY.Sort Key1:=rngXY.Cells(1, 1), Order1:=xlAscending, Orientation:=xlLeftToRight Else rngXY.Sort Key1:=rngXY.Cells(1, 1), Order1:=xlAscending End If 'More code End Function ******************* -- ______ Regards, Greg |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort an array
Tom,
I tried and it does not help. Please see my reply to Tim, I think it might be the issue. Thanks, -- ______ Regards, Greg "Tom Ogilvy" wrote: I don't know what you input ranges look like, but perhaps what you want is Try changing Set rngXY = Union(inputx, inputy) to Set rngXY = InputX.Parent.Range(inputx,inputY) -- Regards, Tom Ogilvy "Greg" wrote in message ... Hi, I have a following Function and the sort does not appear to be working :( . Any help is greatly appreciated. ******************* Function interpolate(kind As String, x As Long, inputx As Range, inputy As Range) Dim n As Integer, i As Integer, j As Integer, index As Integer n = inputx.Cells.Count ' I.Combine Xs in Ys into one range and sort it Dim rngXY As Range Set rngXY = Union(inputx, inputy) If rngXY.Rows.Count < rngXY.Columns.Count Then rngXY.Sort Key1:=rngXY.Cells(1, 1), Order1:=xlAscending, Orientation:=xlLeftToRight Else rngXY.Sort Key1:=rngXY.Cells(1, 1), Order1:=xlAscending End If 'More code End Function ******************* -- ______ Regards, Greg |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort an array
Greg,
If you want to sort within a worksheet UDF then you'll need to create a routine to do the sorting: you can't use the worksheets Sort method. Tim. "Greg" wrote in message ... Yes, I am calling it from a worksheet. For ex, =interpolate("linear",value,C3:K3,C4:K4) But I thought I could work with a range inside the VBA by creating an "independent" range rngXY. Apparently, I need to create an array and work with it? How would I sort an array then? Thanks -- ______ Regards, Greg "Tim Williams" wrote: Are you calling the function from a worksheet? UDF's cannot change the workbook in any way: they can only return values. Tim |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort an array
http://www.standards.com/Sorting/Sor...scription.html
has some sorting algorithms http://support.microsoft.com/default...b;en-us;169617 HOWTO: Sort Algorithms for Numeric Arrays http://vbnet.mvps.org/code/sort/index.html Randy Birch's site -- Regards, Tom Ogilvy "Tim Williams" <saxifrax at pacbell dot net wrote in message ... Greg, If you want to sort within a worksheet UDF then you'll need to create a routine to do the sorting: you can't use the worksheets Sort method. Tim. "Greg" wrote in message ... Yes, I am calling it from a worksheet. For ex, =interpolate("linear",value,C3:K3,C4:K4) But I thought I could work with a range inside the VBA by creating an "independent" range rngXY. Apparently, I need to create an array and work with it? How would I sort an array then? Thanks -- ______ Regards, Greg "Tim Williams" wrote: Are you calling the function from a worksheet? UDF's cannot change the workbook in any way: they can only return values. Tim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort an array | Excel Programming | |||
Array sort | Excel Programming | |||
Array... Sort of... | Excel Worksheet Functions | |||
Sort an Array | Excel Programming | |||
sort an array | Excel Programming |