Refer to CurrentRegion in a UDF?

[Gruuuu]

I'm working on a couple functions to use in a larger project. I want two distinct functions, one that returns an array, and one that lists those array values beneath the cell that contains the function.

I have no problems with the first function, works exactly as intended.

My problem is the second function, I want to place the values of this array beneath the cell that contains this function. I thought I could use CurrentRegion, but I can't. I don't want to use Activecell, because if this function recalculates, it will list beneath whatever I have selected.

functions as follows:

Function getList(ByVal listname As String)

Dim list As Variant
Dim lists As Worksheet
Dim listHead As Range
Dim i As Integer
'Debug.Assert 0

Set lists = Sheets("Lists")

Set listHead = lists.Range("1:1").Find(listname)

getList = Range(listHead.Offset(1, 0), listHead.End(xlDown))

End Function

----------------------------------------------------------

Function xlgetList(ByVal listname As String)
Dim i As Integer
Dim list As Variant

'Debug.Assert 0

xlgetList = listname
list = getList(listname)

With [red][b]CurrentRegion[/b][/red] [green]'If the formula is in F2, I want F2 to be 'listname' and the cells beneath it to hold the array values[/green]
For i = 1 To UBound(list)
    .Offset(i, 0) = list(i, 1)
Next i
End With

End Function

 

[PHV]

Anyway, an UDF can't write to a sheet ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gruuuu]

And apparently I don't even know what currentregion means, so... yeah. ok, back to the drawing board.

 

[DougAJ4]

My problem is the second function, I want to place the values of this array beneath the cell that contains this function. I thought I could use CurrentRegion, but I can't. I don't want to use Activecell, because if this function recalculates, it will list beneath whatever I have selected.

If I understand you correctly, you can assign the array to the function return value, then enter the function as an array function (press ctrl-shift-enter).

Doug Jenkins

http://newtonexcelbach.wordpress.com/

 

[Gruuuu]

I decided to go the route of returning a comma delimited list, and if and when (and will) I need to return values to a spreadsheet, I will use the split function in whatever subroutine that calls it.

And in case anyone is confused, I'm doing it this way because I have several processes which need the list returned. Sometimes it needs to be returned to the spreadsheet, and sometimes it needs to be returned to another process to handle.

I plan on posting an informative thread on here once I get all the kinks worked out. I have a feeling other people may find the setup useful.