J
Joe Adams
Follow-up of 10-Jul discussion. Please Help me again
(especially Charles of Decision Models).
I know I have missed something fundimental, again.
The UDF worked on the 11th but not today?? - so I have
added some additional error handleing code, to no avail.
Symptom - the UDF gives me a #Value! error if I double
click to edit a cell with the UDF, do nothing, and hit a
return. All other cells which have the UDF return #Name? .
I have a variable range, rng_FormsList, which I would like
to sort actively within the worksheet.
I call the function (listed below) with a range string and
a positional element number to return that is located in a
reference cell:
=SortListElem("rng_FormsList",$G14)
where,
rng_FormsList = OFFSET('Unique Lists'!$V$11,0,0,
COUNTIF('Unique Lists'!$V:$V,">"""),1)
and say $G14 = 4
Note: To assure that I do not ask for an element larger
than the length of the range I have an if statement check
for validity.
Therefore, the formula in cell W14 is:
=IF($G14>$N$9,"",
SortListElem("rng_FormsList",$G14))
Now, I copy this formula to cells W15:W25.
RESULT: See above symtoms.
During debugging, I do not get any values passed to the
intermediate window through the Debug.Print statements.
Any Ideas???
Thanks for your help in advance,
Joe Adams
Private Function SortListElem(st_List As Range,
m As Variant) As Variant
On Error GoTo FuncFailed
''' Dim Variables
Dim varSItems As Variant
Dim varSItems2() As Variant
Dim k As Long
Dim n As Long
If Not IsEmpty(st_List) Then
Debug.Print IsEmpty(st_List)
End If
If IsEmpty(m) Then
Debug.Print m
End If
Debug.Print m
If m <= 0 Then GoTo ErrTrap
varSItems = st_List.Value
If Not IsArray(varSItems) Then
If m > 1 Then GoTo ErrTrap
SortListElem = varSItems ' Single Item
Else
n = UBound(varSItems, 1)
If m > n Then GoTo ErrTrap
' Put 2 dimensional range array into a
' single dim array
' ReDim varSItems2(1 To n)
For k = 1 To n
varSItems2(k) = varSItems(k, 1)
Next k
Call dhQuickSort(varArray:=varSItems2)
SortListElem = varSItems2(m)
End If
Exit Function
ErrTrap:
SortListElem = vbNullString
Exit Function
FuncFailed:
SortListElem = CVErr(xlErrValue)
Debug.Print CVErr(xlErrValue)
End Function
(especially Charles of Decision Models).
I know I have missed something fundimental, again.
The UDF worked on the 11th but not today?? - so I have
added some additional error handleing code, to no avail.
Symptom - the UDF gives me a #Value! error if I double
click to edit a cell with the UDF, do nothing, and hit a
return. All other cells which have the UDF return #Name? .
I have a variable range, rng_FormsList, which I would like
to sort actively within the worksheet.
I call the function (listed below) with a range string and
a positional element number to return that is located in a
reference cell:
=SortListElem("rng_FormsList",$G14)
where,
rng_FormsList = OFFSET('Unique Lists'!$V$11,0,0,
COUNTIF('Unique Lists'!$V:$V,">"""),1)
and say $G14 = 4
Note: To assure that I do not ask for an element larger
than the length of the range I have an if statement check
for validity.
Therefore, the formula in cell W14 is:
=IF($G14>$N$9,"",
SortListElem("rng_FormsList",$G14))
Now, I copy this formula to cells W15:W25.
RESULT: See above symtoms.
During debugging, I do not get any values passed to the
intermediate window through the Debug.Print statements.
Any Ideas???
Thanks for your help in advance,
Joe Adams
Private Function SortListElem(st_List As Range,
m As Variant) As Variant
On Error GoTo FuncFailed
''' Dim Variables
Dim varSItems As Variant
Dim varSItems2() As Variant
Dim k As Long
Dim n As Long
If Not IsEmpty(st_List) Then
Debug.Print IsEmpty(st_List)
End If
If IsEmpty(m) Then
Debug.Print m
End If
Debug.Print m
If m <= 0 Then GoTo ErrTrap
varSItems = st_List.Value
If Not IsArray(varSItems) Then
If m > 1 Then GoTo ErrTrap
SortListElem = varSItems ' Single Item
Else
n = UBound(varSItems, 1)
If m > n Then GoTo ErrTrap
' Put 2 dimensional range array into a
' single dim array
' ReDim varSItems2(1 To n)
For k = 1 To n
varSItems2(k) = varSItems(k, 1)
Next k
Call dhQuickSort(varArray:=varSItems2)
SortListElem = varSItems2(m)
End If
Exit Function
ErrTrap:
SortListElem = vbNullString
Exit Function
FuncFailed:
SortListElem = CVErr(xlErrValue)
Debug.Print CVErr(xlErrValue)
End Function