UDF Not Recalculating!

[sxhall]

Hi,

I have previously posted on here for some help and was very lucky to have a UDF written for me, see the thread on below link...

http://www.mrexcel.com/forum/excel-questions/781121-lookup-across-multiple-columns-lists-cells.html

However I now have an issue whereby it works but it is not showing the result in the cells it is being used in! It does however recalculate if I click in the formula bar and then hit return but I need to do this for each cell it is in. It also recalculates sometimes when I flick in to the the VBA code to view it!

This is the code I have...

Public Function ArrayLookup(LookUp_Value As String, LookUp_Range As Range, LookUp_Results As Range) As String


Dim varTest As Variant
Dim iCount As Integer
Dim irow As Integer
Dim srow As Long, erow As Long, scol As Long, ecol As Long, rcol As Long, x As Long
Dim myStr As String, strEMP As String

If Not LookUp_Range.Rows.Count = LookUp_Results.Rows.Count _
And Not LookUp_Range.Rows(1).Row = LookUp_Results.Rows(1).Row Then
ArrayLookup = CVErr(xlErrRef)
Exit Function
End If


srow = LookUp_Range.Rows(1).Row
erow = LookUp_Range.Rows.Count + srow - 1
scol = LookUp_Range.Columns(1).Column
ecol = LookUp_Range.Columns.Count + scol - 1
rcol = LookUp_Results.Column
strEMP = Empty


For irow = srow To erow
myStr = Empty
For Each varTest In Range(Cells(irow, scol), Cells(irow, ecol))
If myStr = Empty Then
myStr = "|" & Trim(varTest) & "|"
Else
myStr = myStr & Trim(varTest) & "|"
End If
Next varTest

myStr = Replace(myStr, ",", "|")
myStr = Replace(myStr, " ", "")

If myStr Like "*|" & LookUp_Value & "|*" Then
If strEMP = Empty Then
strEMP = Cells(irow, rcol)
Else
strEMP = strEMP & Chr(10) & Cells(irow, rcol)
End If
End If
Next irow


ArrayLookup = strEMP


End Function

I've looked online and it seems to be something to do with the the code not being volatile (?).

In short can someone help as to what needs to be added to make it volatile so it recalculates when the lookup value is changed please?

Thanks

Steven

 

[hippiehacker]

try

Public Function ArrayLookup(LookUp_Value As String, LookUp_Range As Range, LookUp_Results As Range) As String
Application.Volatile

Dim varTest As Variant
Dim iCount As Integer
Dim irow As Integer
Dim srow As Long, erow As Long, scol As Long, ecol As Long, rcol As Long, x As Long
Dim myStr As String, strEMP As String

If Not LookUp_Range.Rows.Count = LookUp_Results.Rows.Count _
And Not LookUp_Range.Rows(1).Row = LookUp_Results.Rows(1).Row Then
ArrayLookup = CVErr(xlErrRef)
Exit Function
End If


srow = LookUp_Range.Rows(1).Row
erow = LookUp_Range.Rows.Count + srow - 1
scol = LookUp_Range.Columns(1).Column
ecol = LookUp_Range.Columns.Count + scol - 1
rcol = LookUp_Results.Column
strEMP = Empty


For irow = srow To erow
myStr = Empty
For Each varTest In Range(Cells(irow, scol), Cells(irow, ecol))
If myStr = Empty Then
myStr = "|" & Trim(varTest) & "|"
Else
myStr = myStr & Trim(varTest) & "|"
End If
Next varTest

myStr = Replace(myStr, ",", "|")
myStr = Replace(myStr, " ", "")

If myStr Like "*|" & LookUp_Value & "|*" Then
If strEMP = Empty Then
strEMP = Cells(irow, rcol)
Else
strEMP = strEMP & Chr(10) & Cells(irow, rcol)
End If
End If
Next irow


ArrayLookup = strEMP


End Function

 

[sxhall]

try

Thanks Hippiehacker, worked fine with that change.