| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Working with named ranges | Excel Programming | |||
| Working with named ranges | Excel Programming | |||
| Working with Ranges...Need help | Excel Discussion (Misc queries) | |||
| working with ranges | Excel Programming | |||
| Working with ranges | Excel Programming | |||
Linear Mode
