sort an array

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
*******************
 
Are you calling the function from a worksheet?
UDF's cannot change the workbook in any way: they can only return values.

Tim
 
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)
 
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 said:
Are you calling the function from a worksheet?
UDF's cannot change the workbook in any way: they can only return values.

Tim
Click to expand...
 
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 said:
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)
Click to expand...
 
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.
 
Back
Top