Can't Replicate Success of Nested IF with VBA Code

[X-Elle]

Hello everyone!

I have a nested IF statement that works perfectly if put directly into the cell on the spreadsheet side, but cannot get it to work if I try to use it as VBA Code instead.

The set up I am using is based on a series of "Yes" "No" selections:

<= 8 "Yes" = Pink Elephants "on Parade"

> 8 And < 13 "Yes" = Pink Elephants "on Vacay"

Else (anything greater than 13) = Pink Elephants "sound asleep"


Here is the nested IF statement that is directly put into the cell:

=IF(C21<=8,"on Parade",IF(AND(C21>8,C21<13),"on Vacay","sound asleep"))


As mentioned before, it works as should if entered directly into the cell. But when coding the same in VBA:

Sub PinkElephants()
If Cells(21, 2) <= 8 Then
Cells(23, 2) = "on Parade"
ElseIf Cells(21, 2) > 8 And Cells(21, 2) < 13 Then
Cells(23, 2) = "on Vacay"
Else
Cells(23, 2) = "sound asleep"
End If
End Sub

The cell will only display Pink Elephants "on Parade", and does not change even once the number of "Yes" selections are 9 or greater. Any idea why it is not working?

I have attached two spread sheets to detail both scenarios (one using only the nested IF statement directly in the cell, and the other spreadsheet only using VBA code).

[Norie]

The formula that counts the results is in C21 - Cells(21,3), not B21 - Cells(21,2) as you have in the code.

[X-Elle]

The formula that counts the results is in C21 - Cells(21,3), not B21 - Cells(21,2) as you have in the code.

Thanks Norie! I completely missed that.

Now that the code executes, is there any way to have Cell(23, 2) auto-update itself without having to run the VB Macro (F5) each time? For instance, with 9 "Yes" selections, Cell(23, 2) shows the default "on Parade", but once I hit F5 to run the macro, it updates to "on Vacay". I would like it to auto-update once the "Yes" selections breach the thresholds I've mandated.

Is this possible?

[Norie]

Put this in the sheet 'SaaS Scorecard' module.

Please Login or Register  to view this content.

[X-Elle]

Put this in the sheet 'SaaS Scorecard' module.

Please Login or Register  to view this content.

Solved!

Thanks again! Would love to research this particular macro execution further. Any suggestions on where to start (subject)? I have the Excel 2013 Power Programming with VBA book.