Working with name ranges in vba

[Alejandro Miron]

Im trying to put this into VBA, so that I can chain more than 7 If's

In a cell I have

=INDEX(A5:A10,MATCH(A1,IF(A2="rangename",rangename,0),0)) This statement
works

Im trying to substitute the If statement with a Function like this

=INDEX(A5:A10,MATCH(A1,CatValue(A2),0))

where catValue is the following function


Function CatValue(pVal As String) As String

If pVal = [sheet1!A2] Then
CatValue = "Rangename1"

ElseIf pVal = [sheet1!A3] Then
CatValue = "Rangename2"

Else
CatValue = "0"
End If

End Function


But The result I get is #value!

But when I test the function just displaying the result by putting
=CatValue(A2) in a cell it does give me the result = Rangename1

Thank you very much in advance

Alex

 

[Gary Keramidas]

never work with functions, but maybe select case would work here, too.

Select Case pval
Case [Sheet1!A2]
CatValue = "Rangename1"
Case [sheet1!A3]
CatValue = "Rangename2"
End Select"

 

[Gary Keramidas]

i forgot you case else statment

Select Case pval
Case [Sheet1!A2]
CatValue = "Rangename1"
Case [sheet1!A3]
CatValue = "Rangename2"
Case Else
CatValue = "0"
End Select

 

[Gary Keramidas]

i forgot your case else statement:

Select Case pval
Case [Sheet1!A2]
CatValue = "Rangename1"
Case [sheet1!A3]
CatValue = "Rangename2"
Case Else
CatValue = "0"
End Select

 

[Bob Phillips]

Function CatValue(pVal As String) As Range

If pVal = Worksheets("Sheet3").Range("A2").Value Then
Set CatValue = Range("Rangename1")

ElseIf pVal = Worksheets("Sheet3").Range("A3").Value Then
Set CatValue = Range("Rangename2")

Else
CatValue = "0"
End If

End Function


--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)