HELP!! VBA Nested IF Function

[chad.carmichael]

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

 

[JLGWhiz]

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.

 

[FSt1]

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

 

[John Bundy]

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

 

[FSt1]

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

 

[Jim Thomlinson]

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.

 

[Jim Thomlinson]

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