Hi, very new to VB code, I just found a code online and was trying to convert it to work as I want. Pretty much, as you can see in the code below, I want the cell to say Hi, if the referenced cell has a 1 in it. But when I try it in excel, i get a #VALUE! error. if I put in a 2, I do get the correct return, and putting in anything else gets a 0, as it supposed to. But how do I get text to return in the cell? I tried changing cell format to text, but that didnt work.

And I need to use VB because I will end up having more than 7 IF statements, which I found out, is the most excel can handle.

Thanks.

Function CalcValue(pVal As String) As Long
Select Case pVal
Case "1"
CalcValue = "hi"
Case "2"
CalcValue = 64
Case Else
CalcValue = 0
End Select
End Function

Either of these modifications works.

I would go with second one personally so Case numbers are numbers, not
text.

Function CalcValue(pVal As String)
Select Case pVal
Case "1"
CalcValue = "hi"
Case "2"
CalcValue = 64
Case Else
CalcValue = 0
End Select
End Function


Function CalcValue(pVal As Long)
Select Case pVal
Case 1
CalcValue = "hi"
Case 2
CalcValue = 64
Case Else
CalcValue = 0
End Select
End Function


Gord


On Tue, 28 Feb 2012 14:17:43 +0000, nappyjim
wrote:


Hi, very new to VB code, I just found a code online and was trying to
convert it to work as I want. Pretty much, as you can see in the code
below, I want the cell to say Hi, if the referenced cell has a 1 in it.
But when I try it in excel, i get a #VALUE! error. if I put in a 2, I
do get the correct return, and putting in anything else gets a 0, as it
supposed to. But how do I get text to return in the cell? I tried
changing cell format to text, but that didnt work.

And I need to use VB because I will end up having more than 7 IF
statements, which I found out, is the most excel can handle.

Thanks.

Function CalcValue(pVal As String) As Long
Select Case pVal
Case "1"
CalcValue = "hi"
Case "2"
CalcValue = 64
Case Else
CalcValue = 0
End Select
End Function

Hi nappyjim,

With choice "1", you try to return a text ("hi") into a "long" format
(Function ... as Long).

Try with the following :
Function CalcValue(...)
(rest is the same).

@+
FxM



Le 28/02/2012 15:17, nappyjim a écrit :
Hi, very new to VB code, I just found a code online and was trying to
convert it to work as I want. Pretty much, as you can see in the code
below, I want the cell to say Hi, if the referenced cell has a 1 in it.
But when I try it in excel, i get a #VALUE! error. if I put in a 2, I
do get the correct return, and putting in anything else gets a 0, as it
supposed to. But how do I get text to return in the cell? I tried
changing cell format to text, but that didnt work.

And I need to use VB because I will end up having more than 7 IF
statements, which I found out, is the most excel can handle.

Thanks.

Function CalcValue(pVal As String) As Long
Select Case pVal
Case "1"
CalcValue = "hi"
Case "2"
CalcValue = 64
Case Else
CalcValue = 0
End Select
End Function