formula works, functionworks, but not together

[Mark Kubicki]

I have a formula that works when referencing a specific cell
=VLOOKUP(B13,ProjectUpset,3,FALSE) -in this case I've entered
"0-9876-10" into the cell

I have a custom function that correctly parses information form a string...
=findstring(B13) -in this case, the cell contains "Total 0-9876-10
Personal/Religious", and is parsed to return a value of "0-9876-10"

the function's code is:
Public Function Findstring(vCell)
Dim vStr As String
vStr = ""
Dim vEndNumber As Long

On Error Resume Next
vStr = Mid(vCell.Value, 7, Len(vCell.Value) - 7)
vEndNumber = InStr(1, vStr, " ")
Findstring = Mid(vStr, 1, vEndNumber)
End Function

however, when I insert the function into the formula, I get a #N/A
=VLOOKUP(findstring(B13),ProjectUpset,3,FALSE) - in this case, the cell
contains "Total 0-9876-10 Personal/Religious"


so, I'm confused; and any suggestions would be greatly appreciated
thanks in advance,
mark

 

[Ron Rosenfeld]

I have a formula that works when referencing a specific cell
=VLOOKUP(B13,ProjectUpset,3,FALSE) -in this case I've entered
"0-9876-10" into the cell

I have a custom function that correctly parses information form a string...
=findstring(B13) -in this case, the cell contains "Total 0-9876-10
Personal/Religious", and is parsed to return a value of "0-9876-10"

the function's code is:
Public Function Findstring(vCell)
Dim vStr As String
vStr = ""
Dim vEndNumber As Long

On Error Resume Next
vStr = Mid(vCell.Value, 7, Len(vCell.Value) - 7)
vEndNumber = InStr(1, vStr, " ")
Findstring = Mid(vStr, 1, vEndNumber)
End Function

however, when I insert the function into the formula, I get a #N/A
=VLOOKUP(findstring(B13),ProjectUpset,3,FALSE) - in this case, the cell
contains "Total 0-9876-10 Personal/Religious"


so, I'm confused; and any suggestions would be greatly appreciated
thanks in advance,
mark

Most likely, your Findstring function is returning a trailing space appended to
the value, so the values are not what you think they should be.
--ron

 

[Joel]

Your function is returning 0-9876-10 (with a trialing space like Ron said).
to remove the trailing space make this change

from
vEndNumber = InStr(1, vStr, " ")

to
vEndNumber = InStr(1, vStr, " ") - 1