| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP!! VBA Nested IF Function
I am trying to create a VB function with a nested if statement. I
know how to do this in Excel but I have 16 different options I need to choose from and Excel of course limits you at 7. Here is the code I have written so far. The d10, l10 etc are the cell numbers in excel. My problem is when I put this in excel, it doesn't recognize the numerical values in the spreadsheet from each cell so it always says Unkown Bank instead of the 16 listed banks to choose from. The way it's setup now, D96 is the correct answer, but I'm not getting anywhere. Any help!?! Function CalcValue(pVal As String) If pVal = D10 Then CalcValue = A10 ElseIf pVal = L10 Then CalcValue = I10 ElseIf pVal = D36 Then CalcValue = A36 ElseIf pVal = L36 Then CalcValue = I36 ElseIf pVal = D70 Then CalcValue = A70 ElseIf pVal = L70 Then CalcValue = I70 ElseIf pVal = D96 Then CalcValue = A96 ElseIf pVal = L96 Then CalcValue = I96 ElseIf pVal = D130 Then CalcValue = A130 ElseIf pVal = L130 Then CalcValue = I130 ElseIf pVal = D156 Then CalcValue = A156 ElseIf pVal = L156 Then CalcValue = I156 ElseIf pVal = D190 Then CalcValue = A190 ElseIf pVal = L190 Then CalcValue = I190 ElseIf pVal = D216 Then CalcValue = A216 ElseIf pVal = L216 Then CalcValue = I216 Else CalcValue = "Unknown Bank" End If End Function |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HELP!! VBA Nested IF Function
I assume by your variable "CalcValue" that the cell entries are expected to
be integers or currency. Since you the If statement will not throw a type mismatch error you will need to check the number format of the cell entries to be sure they are the correct data type for pVal to equate to. That is as much as I can deduce from what I see. " wrote: I am trying to create a VB function with a nested if statement. I know how to do this in Excel but I have 16 different options I need to choose from and Excel of course limits you at 7. Here is the code I have written so far. The d10, l10 etc are the cell numbers in excel. My problem is when I put this in excel, it doesn't recognize the numerical values in the spreadsheet from each cell so it always says Unkown Bank instead of the 16 listed banks to choose from. The way it's setup now, D96 is the correct answer, but I'm not getting anywhere. Any help!?! Function CalcValue(pVal As String) If pVal = D10 Then CalcValue = A10 ElseIf pVal = L10 Then CalcValue = I10 ElseIf pVal = D36 Then CalcValue = A36 ElseIf pVal = L36 Then CalcValue = I36 ElseIf pVal = D70 Then CalcValue = A70 ElseIf pVal = L70 Then CalcValue = I70 ElseIf pVal = D96 Then CalcValue = A96 ElseIf pVal = L96 Then CalcValue = I96 ElseIf pVal = D130 Then CalcValue = A130 ElseIf pVal = L130 Then CalcValue = I130 ElseIf pVal = D156 Then CalcValue = A156 ElseIf pVal = L156 Then CalcValue = I156 ElseIf pVal = D190 Then CalcValue = A190 ElseIf pVal = L190 Then CalcValue = I190 ElseIf pVal = D216 Then CalcValue = A216 ElseIf pVal = L216 Then CalcValue = I216 Else CalcValue = "Unknown Bank" End If End Function |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HELP!! VBA Nested IF Function
hi
D10 is a range. D10.value is the value of range D10. try putting .value at the end of each range adress. Regards FSt1 " wrote: I am trying to create a VB function with a nested if statement. I know how to do this in Excel but I have 16 different options I need to choose from and Excel of course limits you at 7. Here is the code I have written so far. The d10, l10 etc are the cell numbers in excel. My problem is when I put this in excel, it doesn't recognize the numerical values in the spreadsheet from each cell so it always says Unkown Bank instead of the 16 listed banks to choose from. The way it's setup now, D96 is the correct answer, but I'm not getting anywhere. Any help!?! Function CalcValue(pVal As String) If pVal = D10 Then CalcValue = A10 ElseIf pVal = L10 Then CalcValue = I10 ElseIf pVal = D36 Then CalcValue = A36 ElseIf pVal = L36 Then CalcValue = I36 ElseIf pVal = D70 Then CalcValue = A70 ElseIf pVal = L70 Then CalcValue = I70 ElseIf pVal = D96 Then CalcValue = A96 ElseIf pVal = L96 Then CalcValue = I96 ElseIf pVal = D130 Then CalcValue = A130 ElseIf pVal = L130 Then CalcValue = I130 ElseIf pVal = D156 Then CalcValue = A156 ElseIf pVal = L156 Then CalcValue = I156 ElseIf pVal = D190 Then CalcValue = A190 ElseIf pVal = L190 Then CalcValue = I190 ElseIf pVal = D216 Then CalcValue = A216 ElseIf pVal = L216 Then CalcValue = I216 Else CalcValue = "Unknown Bank" End If End Function |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HELP!! VBA Nested IF Function
First make sure your function is in a module, if it is, then it doesn't know
what sheet you are talking about when you say D10. That would look like this Function CalcValue(pVal As String) If pVal = ActiveSheet.Range("D10") Then CalcValue = ActiveSheet.Range("A10") ElseIf pVal = ActiveSheet.Range("L10") Then CalcValue = ActiveSheet.Range("I10") ElseIf pVal = ActiveSheet.Range("D36") Then CalcValue = ActiveSheet.Range("A36") ElseIf pVal = ActiveSheet.Range("L36") Then CalcValue = ActiveSheet.Range("I36") ElseIf pVal = ActiveSheet.Range("D70") Then CalcValue = ActiveSheet.Range("A70") ElseIf pVal = ActiveSheet.Range("L70") Then CalcValue = ActiveSheet.Range("I70") ElseIf pVal = ActiveSheet.Range("D96") Then CalcValue = ActiveSheet.Range("A96") ElseIf pVal = ActiveSheet.Range("L96") Then CalcValue = ActiveSheet.Range("I96") ElseIf pVal = ActiveSheet.Range("D130") Then CalcValue = ActiveSheet.Range("A130") ElseIf pVal = ActiveSheet.Range("L130") Then CalcValue = ActiveSheet.Range("I130") ElseIf pVal = ActiveSheet.Range("D156") Then CalcValue = ActiveSheet.Range("A156") ElseIf pVal = ActiveSheet.Range("L156") Then CalcValue = ActiveSheet.Range("I156") ElseIf pVal = ActiveSheet.Range("D190") Then CalcValue = ActiveSheet.Range("A190") ElseIf pVal = ActiveSheet.Range("L190") Then CalcValue = ActiveSheet.Range("I190") ElseIf pVal = ActiveSheet.Range("D216") Then CalcValue = ActiveSheet.Range("A216") ElseIf pVal = ActiveSheet.Range("L216") Then CalcValue = ActiveSheet.Range("I216") Else CalcValue = "Unknown Bank" End If End Function -- -John Please rate when your question is answered to help us and others know what is helpful. " wrote: I am trying to create a VB function with a nested if statement. I know how to do this in Excel but I have 16 different options I need to choose from and Excel of course limits you at 7. Here is the code I have written so far. The d10, l10 etc are the cell numbers in excel. My problem is when I put this in excel, it doesn't recognize the numerical values in the spreadsheet from each cell so it always says Unkown Bank instead of the 16 listed banks to choose from. The way it's setup now, D96 is the correct answer, but I'm not getting anywhere. Any help!?! Function CalcValue(pVal As String) If pVal = D10 Then CalcValue = A10 ElseIf pVal = L10 Then CalcValue = I10 ElseIf pVal = D36 Then CalcValue = A36 ElseIf pVal = L36 Then CalcValue = I36 ElseIf pVal = D70 Then CalcValue = A70 ElseIf pVal = L70 Then CalcValue = I70 ElseIf pVal = D96 Then CalcValue = A96 ElseIf pVal = L96 Then CalcValue = I96 ElseIf pVal = D130 Then CalcValue = A130 ElseIf pVal = L130 Then CalcValue = I130 ElseIf pVal = D156 Then CalcValue = A156 ElseIf pVal = L156 Then CalcValue = I156 ElseIf pVal = D190 Then CalcValue = A190 ElseIf pVal = L190 Then CalcValue = I190 ElseIf pVal = D216 Then CalcValue = A216 ElseIf pVal = L216 Then CalcValue = I216 Else CalcValue = "Unknown Bank" End If End Function |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HELP!! VBA Nested IF Function
It is not a matter of what sheet D10 is refering to. What is actually
happening is a variable called D10 of type variant is being declared on the fly with no associated value. Your solution is correct however. The OP does not have option explicit at the top of the code module to ensure that all variables must be declared. Check out this link for more info... http://www.cpearson.com/excel/variables.htm -- HTH... Jim Thomlinson "John Bundy" wrote: First make sure your function is in a module, if it is, then it doesn't know what sheet you are talking about when you say D10. That would look like this Function CalcValue(pVal As String) If pVal = ActiveSheet.Range("D10") Then CalcValue = ActiveSheet.Range("A10") ElseIf pVal = ActiveSheet.Range("L10") Then CalcValue = ActiveSheet.Range("I10") ElseIf pVal = ActiveSheet.Range("D36") Then CalcValue = ActiveSheet.Range("A36") ElseIf pVal = ActiveSheet.Range("L36") Then CalcValue = ActiveSheet.Range("I36") ElseIf pVal = ActiveSheet.Range("D70") Then CalcValue = ActiveSheet.Range("A70") ElseIf pVal = ActiveSheet.Range("L70") Then CalcValue = ActiveSheet.Range("I70") ElseIf pVal = ActiveSheet.Range("D96") Then CalcValue = ActiveSheet.Range("A96") ElseIf pVal = ActiveSheet.Range("L96") Then CalcValue = ActiveSheet.Range("I96") ElseIf pVal = ActiveSheet.Range("D130") Then CalcValue = ActiveSheet.Range("A130") ElseIf pVal = ActiveSheet.Range("L130") Then CalcValue = ActiveSheet.Range("I130") ElseIf pVal = ActiveSheet.Range("D156") Then CalcValue = ActiveSheet.Range("A156") ElseIf pVal = ActiveSheet.Range("L156") Then CalcValue = ActiveSheet.Range("I156") ElseIf pVal = ActiveSheet.Range("D190") Then CalcValue = ActiveSheet.Range("A190") ElseIf pVal = ActiveSheet.Range("L190") Then CalcValue = ActiveSheet.Range("I190") ElseIf pVal = ActiveSheet.Range("D216") Then CalcValue = ActiveSheet.Range("A216") ElseIf pVal = ActiveSheet.Range("L216") Then CalcValue = ActiveSheet.Range("I216") Else CalcValue = "Unknown Bank" End If End Function -- -John Please rate when your question is answered to help us and others know what is helpful. " wrote: I am trying to create a VB function with a nested if statement. I know how to do this in Excel but I have 16 different options I need to choose from and Excel of course limits you at 7. Here is the code I have written so far. The d10, l10 etc are the cell numbers in excel. My problem is when I put this in excel, it doesn't recognize the numerical values in the spreadsheet from each cell so it always says Unkown Bank instead of the 16 listed banks to choose from. The way it's setup now, D96 is the correct answer, but I'm not getting anywhere. Any help!?! Function CalcValue(pVal As String) If pVal = D10 Then CalcValue = A10 ElseIf pVal = L10 Then CalcValue = I10 ElseIf pVal = D36 Then CalcValue = A36 ElseIf pVal = L36 Then CalcValue = I36 ElseIf pVal = D70 Then CalcValue = A70 ElseIf pVal = L70 Then CalcValue = I70 ElseIf pVal = D96 Then CalcValue = A96 ElseIf pVal = L96 Then CalcValue = I96 ElseIf pVal = D130 Then CalcValue = A130 ElseIf pVal = L130 Then CalcValue = I130 ElseIf pVal = D156 Then CalcValue = A156 ElseIf pVal = L156 Then CalcValue = I156 ElseIf pVal = D190 Then CalcValue = A190 ElseIf pVal = L190 Then CalcValue = I190 ElseIf pVal = D216 Then CalcValue = A216 ElseIf pVal = L216 Then CalcValue = I216 Else CalcValue = "Unknown Bank" End If End Function |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HELP!! VBA Nested IF Function
hi agian.
forgot to mention. nested if statedments(limit 7) applies to formulas only. in code you can have as many if statements as you need. (in theory) Regards FSt1 " wrote: I am trying to create a VB function with a nested if statement. I know how to do this in Excel but I have 16 different options I need to choose from and Excel of course limits you at 7. Here is the code I have written so far. The d10, l10 etc are the cell numbers in excel. My problem is when I put this in excel, it doesn't recognize the numerical values in the spreadsheet from each cell so it always says Unkown Bank instead of the 16 listed banks to choose from. The way it's setup now, D96 is the correct answer, but I'm not getting anywhere. Any help!?! Function CalcValue(pVal As String) If pVal = D10 Then CalcValue = A10 ElseIf pVal = L10 Then CalcValue = I10 ElseIf pVal = D36 Then CalcValue = A36 ElseIf pVal = L36 Then CalcValue = I36 ElseIf pVal = D70 Then CalcValue = A70 ElseIf pVal = L70 Then CalcValue = I70 ElseIf pVal = D96 Then CalcValue = A96 ElseIf pVal = L96 Then CalcValue = I96 ElseIf pVal = D130 Then CalcValue = A130 ElseIf pVal = L130 Then CalcValue = I130 ElseIf pVal = D156 Then CalcValue = A156 ElseIf pVal = L156 Then CalcValue = I156 ElseIf pVal = D190 Then CalcValue = A190 ElseIf pVal = L190 Then CalcValue = I190 ElseIf pVal = D216 Then CalcValue = A216 ElseIf pVal = L216 Then CalcValue = I216 Else CalcValue = "Unknown Bank" End If End Function |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HELP!! VBA Nested IF Function
I assume you are trying to check the input value against the values in some
specific cells. There are a couple of issues with what you are doing. Firstly you need to reference the value retuned by the cell and not the cell address (what you actually have is a whole pile of variables or type variant declared on the fly with no value associated with them). Secondly your function will not recalc when the dependant cells are changed. Try this... Function CalcValue(pVal As Range, DependantRange As Range) If pVal = Range("D10").Value Then CalcValue = Range("A10").Value ElseIf pVal = Range("LA10").Value Then CalcValue = Range("I10").Value ElseIf pVal = Range("D36").Value Then CalcValue = Range("A36").Value ElseIf pVal = Range("L36").Value Then CalcValue = Range("I36").Value ElseIf pVal = Range("D70").Value Then CalcValue = Range("A70").Value ElseIf pVal = Range("L70").Value Then CalcValue = Range("I70").Value ElseIf pVal = Range("D96").Value Then CalcValue = Range("A96").Value ElseIf pVal = Range("L96").Value Then CalcValue = Range("I96").Value ElseIf pVal = Range("D130").Value Then CalcValue = Range("A130").Value ElseIf pVal = Range("L130").Value Then CalcValue = Range("I130").Value ElseIf pVal = Range("D156").Value Then CalcValue = Range("A156").Value ElseIf pVal = Range("L156").Value Then CalcValue = Range("I156").Value ElseIf pVal = Range("D190").Value Then CalcValue = Range("A190").Value ElseIf pVal = Range("L190").Value Then CalcValue = Range("I190").Value ElseIf pVal = Range("D216").Value Then CalcValue = Range("A216").Value ElseIf pVal = Range("L216").Value Then CalcValue = Range("I216").Value Else CalcValue = "Unknown Bank" End If End Function And use it like this... =CalcValue(A1, A10:L216) This will recalc each time a cell in the range A10:I216 changes. -- HTH... Jim Thomlinson " wrote: I am trying to create a VB function with a nested if statement. I know how to do this in Excel but I have 16 different options I need to choose from and Excel of course limits you at 7. Here is the code I have written so far. The d10, l10 etc are the cell numbers in excel. My problem is when I put this in excel, it doesn't recognize the numerical values in the spreadsheet from each cell so it always says Unkown Bank instead of the 16 listed banks to choose from. The way it's setup now, D96 is the correct answer, but I'm not getting anywhere. Any help!?! Function CalcValue(pVal As String) If pVal = D10 Then CalcValue = A10 ElseIf pVal = L10 Then CalcValue = I10 ElseIf pVal = D36 Then CalcValue = A36 ElseIf pVal = L36 Then CalcValue = I36 ElseIf pVal = D70 Then CalcValue = A70 ElseIf pVal = L70 Then CalcValue = I70 ElseIf pVal = D96 Then CalcValue = A96 ElseIf pVal = L96 Then CalcValue = I96 ElseIf pVal = D130 Then CalcValue = A130 ElseIf pVal = L130 Then CalcValue = I130 ElseIf pVal = D156 Then CalcValue = A156 ElseIf pVal = L156 Then CalcValue = I156 ElseIf pVal = D190 Then CalcValue = A190 ElseIf pVal = L190 Then CalcValue = I190 ElseIf pVal = D216 Then CalcValue = A216 ElseIf pVal = L216 Then CalcValue = I216 Else CalcValue = "Unknown Bank" End If End Function |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I do a TODAY() Function within a Nested IF AND Function | Excel Discussion (Misc queries) | |||
| Nested IF function | Excel Worksheet Functions | |||
| can you nested sum and round function within if function? | Excel Worksheet Functions | |||
| Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
| Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions | |||
Hybrid Mode
