sort an array

[Greg]

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

[Tim Williams]

Are you calling the function from a worksheet?
UDF's cannot change the workbook in any way: they can only return values.

Tim


"Greg" <[email protected]> wrote in message
news:[email protected]...
> 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

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[Greg]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[Greg]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[Tim Williams]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>>
>>

[Tom Ogilvy]

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
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >>
> >>
>
>