Solved: One Question?

[vishwakarma]

Hi guys,

I have a UDF function which returns multiple entries:-
[vba]
Function VlookupAll(rLookupVal, rTable As Range, lCol As Long)

Dim rCell As Range, Result

VlookupAll = CVErr(xlErrNA)

For Each rCell In rTable
If rCell = rLookupVal Then
Result = Result & "," & rCell.Offset(, lCol - 1)
End If
Next rCell

If Result <> "" Then
Result = Right(Result, Len(Result) - 1)
VlookupAll = Result
End If

End Function
[/vba]

But there is problem,it is taking too much time to process the result.

Is it possible to make it faster ??

My excel file gets stuck/Not Responding when it is calculating...



Thanks,

[GTO]

But there is problem,it is taking too much time to process the result.

Is it possible to make it faster ??

My excel file gets stuck/Not Responding when it is calculating...

Not sure about why its getting 'stuck' but if the range being run through is large, flopping the range's values into an array will speed things up.

Not well tested but something like:
[vba]Function VlookupAll(rLookupVal As Variant, rTable As Range, lCol As Long) As Variant
Dim _
aryRangeVals As Variant, _
Result As String, _
x As Long, _
y As Long

Application.Volatile
VlookupAll = CVErr(xlErrNA)
aryRangeVals = rTable.Resize(, rTable.Columns.Count + lCol - 1).Value

For x = 1 To UBound(aryRangeVals, 1)
For y = 1 To UBound(aryRangeVals, 2) - (lCol - 1)
If rLookupVal = aryRangeVals(x, y) Then
Result$ = Result$ & "," & aryRangeVals(x, y + lCol - 1)
End If
Next
Next

If Len(Result$) Then
VlookupAll = Right(Result$, Len(Result$) - 1)
End If
End Function[/vba] Hope that helps,

Mark

[vishwakarma]

Thanks Mark,

I just restricted the range from whole column to specific column range and the function is working like a charm...

FROM
=VlookupAll(A2,Sheet2!A:B,2)

To something like,

=VlookupAll(A2,Sheet2!$A$1:$B$300,2)


Thanks for the advice..