Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple If function for more than 7 values
Hi all, I have a question regarding VBA code for an if function.
In looking for an answer, I found this code provided on a website (www.techonthenet.com) It seems to be what I'm looking for but returns the error '#NAME' when I try and add it to Excel. Does anyone know what's wrong with the code? The strange thing is that there's an example of this as a download which seems to return the same error, I'm wondering if it's something to do with the version of Excel I'm using... (I have 2003) Function CalcValue(pVal As String) As Long If pVal = "10x12" Then CalcValue = 140 ElseIf pVal = "8x8" Then CalcValue = 64 ElseIf pVal = "6x6" Then CalcValue = 36 ElseIf pVal = "8x10" Then CalcValue = 80 ElseIf pVal = "14x16" Then CalcValue = 224 ElseIf pVal = "9x9" Then CalcValue = 81 ElseIf pVal = "4x3" Then CalcValue = 12 Else CalcValue = 0 End If End Function The values in the code are not the one's I would be using, but they are the ones provided in the example (which also does not seem to work). |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple If function for more than 7 values
It works for me. You have to store the function in a standard code module.
Hit Alt-F11, then InsertModule, copy and paste the code. Then in a worksheet, use either =CalcValue("9x9") or =CalcValue(A1) where A1=9x9 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "echo_park" wrote in message oups.com... Hi all, I have a question regarding VBA code for an if function. In looking for an answer, I found this code provided on a website (www.techonthenet.com) It seems to be what I'm looking for but returns the error '#NAME' when I try and add it to Excel. Does anyone know what's wrong with the code? The strange thing is that there's an example of this as a download which seems to return the same error, I'm wondering if it's something to do with the version of Excel I'm using... (I have 2003) Function CalcValue(pVal As String) As Long If pVal = "10x12" Then CalcValue = 140 ElseIf pVal = "8x8" Then CalcValue = 64 ElseIf pVal = "6x6" Then CalcValue = 36 ElseIf pVal = "8x10" Then CalcValue = 80 ElseIf pVal = "14x16" Then CalcValue = 224 ElseIf pVal = "9x9" Then CalcValue = 81 ElseIf pVal = "4x3" Then CalcValue = 12 Else CalcValue = 0 End If End Function The values in the code are not the one's I would be using, but they are the ones provided in the example (which also does not seem to work). |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple If function for more than 7 values
Hi echo_park It works fine for me, make sure you've got the code in a module or in the code area of the sheet you're using the function in. And, aesthetically, you might like this: Function CalcValue(pVal As String) As Long Select Case pVal Case "10x12" CalcValue = 140 Case "8x8" CalcValue = 64 Case "6x6" CalcValue = 36 Case "8x10" CalcValue = 80 Case "14x16" CalcValue = 224 Case "9x9" CalcValue = 81 Case "4x3" CalcValue = 12 Case Else CalcValue = 0 End Select End Function Regards Steve |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple If function for more than 7 values
The values in the code are not the one's I would be using...
Would this general idea give you something to work with? However, this suggests that 10*12 equals 120 instead of 140. Sub Demo() Dim s, n s = "10x12" 'Simple check If s Like "*x*" Then n = Evaluate(Replace(s, "x", "*")) Debug.Print n ' <- 120 End If End Sub -- HTH. :) Dana DeLouis Windows XP, Office 2003 "echo_park" wrote in message oups.com... Hi all, I have a question regarding VBA code for an if function. In looking for an answer, I found this code provided on a website (www.techonthenet.com) It seems to be what I'm looking for but returns the error '#NAME' when I try and add it to Excel. Does anyone know what's wrong with the code? The strange thing is that there's an example of this as a download which seems to return the same error, I'm wondering if it's something to do with the version of Excel I'm using... (I have 2003) Function CalcValue(pVal As String) As Long If pVal = "10x12" Then CalcValue = 140 ElseIf pVal = "8x8" Then CalcValue = 64 ElseIf pVal = "6x6" Then CalcValue = 36 ElseIf pVal = "8x10" Then CalcValue = 80 ElseIf pVal = "14x16" Then CalcValue = 224 ElseIf pVal = "9x9" Then CalcValue = 81 ElseIf pVal = "4x3" Then CalcValue = 12 Else CalcValue = 0 End If End Function The values in the code are not the one's I would be using, but they are the ones provided in the example (which also does not seem to work). |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple If function for more than 7 values
Hmm... I've done nothing wrong in the entering of the code, I've
checked and triple checked that. Just doesn't work on this PC for some reason. Whatever I do I just get '#NAME' and nothing. I've saved and logged off and made sure everything is as it should be, but if there's no error in the code it must be something on this PC that's messing it up... Thanks for your help anyway! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple If function for more than 7 values
Ok now Excel seems to have changed the error... its a 'VALUE#!' error
now! I selected the formula from the list and it suddenly changed... Any ideas? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple If function for more than 7 values
Ok third post in a row to let you know I've got it sorted now... not
sure exactly how I fixed the problem, but I fiddled around and suddenly things started working! Thanks for the continued help, this forum is great. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
combine multiple values in a table into one cell using vlookup? | Excel Worksheet Functions | |||
How can you use multiple ranges in a function? | Excel Worksheet Functions | |||
return multiple corresponding values in excel | Excel Worksheet Functions | |||
How to sum values in multiple worksheets | Excel Worksheet Functions | |||
Multiple X-Axis Values | Charts and Charting in Excel |