Working with name ranges in vba
 

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

Working with name ranges in vba
 

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


"Alejandro Miron" wrote in message
...

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

Working with name ranges in vba
 

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


"Alejandro Miron" wrote in message
...

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

Working with name ranges in vba
 

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



--


Gary


"Alejandro Miron" wrote in message
...

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

Working with name ranges in vba
 

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)

"Alejandro Miron" wrote in message
...

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