#Value Error when calling VBA Function

[hmltnangel]

Hi Excel people,

I have a Function in VBA that a formula is calling up. But its recently started just giving me a #Value error.Any suggestions?

VBA Function: (Is in a Module in VBA)

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
Dim i As Long
Dim Result As String
For i = 1 To LookupRange.Columns(1).Cells.Count
  If LookupRange.Cells(i, 1) = Lookupvalue Then
    For J = 1 To i - 1
    If LookupRange.Cells(J, 1) = Lookupvalue Then
      If LookupRange.Cells(J, ColumnNumber) = LookupRange.Cells(i, ColumnNumber) Then
        GoTo Skip
      End If
    End If
    Next J
    Result = Result & " " & LookupRange.Cells(i, ColumnNumber) & ","
Skip:
  End If
Next i
MultipleLookupNoRept = Left(Result, Len(Result) - 1)
End Function

Then the formula is:

=MultipleLookupNoRept(A2,Resource,4)

 

[mart37]

What is the address from the range RESOURCE?

 

[hmltnangel]

Thanks Mart,

Resource is a table called Resource on same tab as the formula, covering A1:D580

 

[hmltnangel]

Example:

The expected output would be

B2 - Blank
B3 - BB-BBI, BB-TBE

	A	B	C	D
1	Staff ID	Combined Projects	Name	Project
2	123456	=MultipleLookupNoRept(A2,Resource,4)	Ed Eddie	BB - BBI
3	123456	=MultipleLookupNoRept(A3,Resource,4)	Ed Eddie	BB - TBE

 

[mart37]

I get no error.
Output is:

Staff ID	Combined Projects	Name	Project
123456​	BB - BBI, BB - TBE	Ed Eddie	BB - BBI
123456​	BB - BBI, BB - TBE	Ed Eddie	BB - TBE

 

[hmltnangel]

That would be acceptable, but I get #Value!

What would cause the value error? Formatting somewhere?

 

[mart37]

Resource is a table called Resource on same tab as the formula, covering A1:D580

Can you check the range. On the tab FORMULA is a button NAME MANAGER

 

[hmltnangel]

aaaahhhhh, now we might be somewhere.

In the Name Manager, the "Resource" name is there, but in the Value fields, theres "#N/A!" in some of the values

 

[hmltnangel]

And popping an "IFERROR" into the column causing the errors ...... makes it work fine :D

Thanks Mart would never have worked it out without that pointer.