I need step by step instructions to create a macro for 10 imbedde.

[diana]

I am trying to put 10 nested if statements in a cell. In Excel 2002, it now
only allows 7 embedded if statements. It says I need to make a macro. I need
step by step instructions. Can you help?

 

[Nick Hodge]

Diana

There may be a different way to approach this if we knew what you are trying
to do, but in general you could write a UDF (User Defined Function) with a
Select...End select structure like so: (It takes in a whole number (iInput)
and returns a string corresponding to the entry 1-10)

You use it like any other worksheet function

=Get10Values(A1)

Function Get10Values(iInput As Integer) As String
Application.Volatile True
Select Case iInput
Case Is = 1
Get10Values = "One"
Case Is = 2
Get10Values = "Two"
Case Is = 3
Get10Values = "Three"
Case Is = 4
Get10Values = "Four"
Case Is = 5
Get10Values = "Five"
Case Is = 6
Get10Values = "Six"
Case Is = 7
Get10Values = "Seven"
Case Is = 8
Get10Values = "Eight"
Case Is = 9
Get10Values = "Nine"
Case Is = 10
Get10Values = "Ten"
Case Else
Get10Values = "Out of range"
End Select
End Function


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Ken Wright]

Give us details of what you are trying to do.

 

[Rob van Gelder]

Is it really necessary to go 7 layers deep?

Are there really 128 (2^7) outcomes or just 2 coutcomes?

You might be able to use the AND operator to reduce the layers of IF.

Let us know how you want to proceed?