Solved: UDF Inserting Unwanted Quotation Marks

[tyndale2045]

I have a UDF called Breed_Standard that I am trying to use. My goal is to use range names with the VLOOKUP function (so as to avoid making long and confusing IF formulas). Check out the attached workbook, and you'll see what I mean.

My problem is this: It appears that Excel is enclosing the result of the UDF in quotation marks. The quotes prevent Excel from recognizing said result as a range name. Thus, the formula fails.

What to do?

Any help will be greatly appreciated.

Jim


[Later edit: Woops, I now see that the example I've sent does not actually include the long IF formula in the cell with the second comment. If you want to see the giant IF statement, though, let me know. I'm sure you'll find it as boring and confusing as I do.]

[Norie]

Sorry, but your UDF is doing exactly what you are telling it to.:)

Try this.

=VLOOKUP(D3,INDIRECT(Breed_Standard(C3)),2,FALSE)

[tyndale2045]

Works great! Thanks a bunch Norie.

[tyndale2045]

I'm curious about something (and I'm trying to learn better programming practices, especially about being concise):

You said my UDF is doing exactly what I've told it to do. Is there any way to just alter the UDF so that it functions as I was hoping? That is, is there a way to accomplish what I'm trying to accomplish without using the INDIRECT function?

[I know that Norie is the guru of concise programming, but I'm open to suggestions from anyone.]

Thanks,
Jim

[Bob Phillips]

Function Breed_Standard(Breed_Code As String) As Range
Application.Volatile
Select Case Breed_Code
Case "4/F", "C/F"
Set Breed_Standard = Range("Cobb")
Case "4/L", "1/L"
Set Breed_Standard = Range("Hubbard")
End Select
End Function

[Bob Phillips]

Better to get rid of that nasty Application Volatile



Function Breed_Standard(Breed_Code As Range) As Range
Select Case Breed_Code.Value
Case "4/F", "C/F"
Set Breed_Standard = Range("Cobb")
Case "4/L", "1/L"
Set Breed_Standard = Range("Hubbard")
End Select
End Function


as it is not needed since you pass the range in to the argument, so it will recalculate when it changes