Nested IF statements.

[JayLee]

I have a lot of nested if statements in one cell. Unfortunately, I couldn't add anymore because I believe I hit the limit for the IF statements. Is there another alternative so I may fit more IF-like statements into the cell? Perhaps using the case statement? A formula or VBA code would be helpful. Thanks!

 

[DonkeyOte]

You'll need to paste some data (or just give an example) and your current formula (with the 7 IFs)

Then we can write some code...

Cheers
Luke

 

[JayLee]

"=IF(RC[-7]=""Cancel Sale "",""SL"",IF(RC[-7]=""Sold "", ""sl"", IF(RC[-7]=""Delivered "", ""sl"", IF(RC[-7]=""Received "", ""by"", IF(RC[-7]=""Purchased "", ""by"", IF(RC[-7]=""Reinvest "", ""by"",IF(RC[-7]=""Dividend "", ""dv"","""")))))))"

There's the nested if in VBA code. I hope to be able to add some more. Thanks!

 

[Tom Urtis]

You'll need to adjust the following code to fit the environment of your existing macro, but here might be one way to transform your nested If statements to a Select Case structure:

Sub FromIfToSelectCase()
Select Case ActiveCell.Offset(0, -7).Value
Case "Cancel Sale"
ActiveCell.Value = "SL"
Case "Sold", "Delivered"
ActiveCell.Value = "sl"
Case "Received", "Purchased", "Reinvest"
ActiveCell.Value = "by"
Case "Dividend"
ActiveCell.Value = "dv"
Case Else
ActiveCell.Value = ""
End Select
End Sub

 

[DonkeyOte]

that's excellent Tom - I had never seen Case used before - and have learnt something new!

Cheers!

Luke