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
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