Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

Convert IF to Case

Folks,
I am not very good at the Case statement. The code below will not work, so I thought maybe Case would be a better option?

Private Sub cmdCkItems_Click()

If Range("C4").Text = "network" And Range("C5").Text = "7" Then
    Range("E4").Locked = False
    Range("E4").Font.Color = vbWhite
    Range("E4").Interior.Color = vbRed
    Range("E4").Value = "Correct"
Else
    Range("E4").Value = "Error"
    Range("E4").Font.Color = vbYellow
    Range("E4").Interior.Color = vbBlack
    Range("E4").Locked = True
    CheckFormulaFunction
    Exit Sub
End If

If Range("C4").Text = "components" And Range("C5").Text = "19" Then
    Range("E4").Locked = False
    Range("E4").Font.Color = vbWhite
    Range("E4").Interior.Color = vbRed
    Range("E4").Value = "Correct"
Else
    Range("E4").Value = "Error"
    Range("E4").Font.Color = vbYellow
    Range("E4").Interior.Color = vbBlack
    Range("E4").Locked = True
    CheckFormulaFunction
    Exit Sub
End If

FormulasOK

End Sub

Open in new window

0
Frank Freese
Asked:
Frank Freese
  • 6
  • 4
  • 2
2 Solutions
 
Martin LissOlder than dirtCommented:
When you say it "will not work", what happens?
0
 
Ken ButtersCommented:
Based on what I see, I don't think a case statement would be a better solution.  The reason I say that is it looks like your if condition is based on multiple criteria.

Looks to me like the following might be your intention but is just a guess without proper context of what you are trying to do:

Private Sub cmdCkItems_Click()

If Range("C4").text = "network" And Range("C5").text = "7" Then
    Range("E4").Locked = False
    Range("E4").Font.Color = vbWhite
    Range("E4").Interior.Color = vbRed
    Range("E4").Value = "Correct"
ElseIf Range("C4").text = "components" And Range("C5").text = "19" Then
    Range("E4").Locked = False
    Range("E4").Font.Color = vbWhite
    Range("E4").Interior.Color = vbRed
    Range("E4").Value = "Correct"
Else
    Range("E4").Value = "Error"
    Range("E4").Font.Color = vbYellow
    Range("E4").Interior.Color = vbBlack
    Range("E4").Locked = True
    CheckFormulaFunction
    Exit Sub
End If

FormulasOK

End Sub

Open in new window

or another variation that would do the same thing in this example:
Private Sub cmdCkItems_Click()

If (Range("C4").text = "network" And Range("C5").text = "7") Or (Range("C4").text = "components" And Range("C5").text = "19") Then
    Range("E4").Locked = False
    Range("E4").Font.Color = vbWhite
    Range("E4").Interior.Color = vbRed
    Range("E4").Value = "Correct"
Else
    Range("E4").Value = "Error"
    Range("E4").Font.Color = vbYellow
    Range("E4").Interior.Color = vbBlack
    Range("E4").Locked = True
    CheckFormulaFunction
    Exit Sub
End If

FormulasOK

End Sub

Open in new window

0
 
Frank FreeseAuthor Commented:
The code always will stop at the first IF statement. I need to evaluate both possible conditions in cell C4
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Martin LissOlder than dirtCommented:
In that case it looks like buttersk's solution will work for you but I disagree with his implication that you can't use a Case statement with multiple conditions.

Private Sub cmdCkItems_Click()

Select Case True
    Case Range("C4").text = "network" And Range("C5").text = "7"
        Range("E4").Locked = False
        Range("E4").Font.Color = vbWhite
        Range("E4").Interior.Color = vbRed
        Range("E4").Value = "Correct"
    Case Range("C4").text = "components" And Range("C5").text = "19"
        Range("E4").Locked = False
        Range("E4").Font.Color = vbWhite
        Range("E4").Interior.Color = vbRed
        Range("E4").Value = "Correct"
    Case Else
        Range("E4").Value = "Error"
        Range("E4").Font.Color = vbYellow
        Range("E4").Interior.Color = vbBlack
        Range("E4").Locked = True
End Select
    CheckFormulaFunction
    Exit Sub
End If

FormulasOK

End Sub

Open in new window

0
 
Ken ButtersCommented:
@martinliss - I didn't think about the fact that you could issue the select case on a boolean value of true, which in turn would allow the equivalent of any IF condition on each subsequent case check.

Nice!  I think I will find uses for that myself!
0
 
Martin LissOlder than dirtCommented:
Thanks. That's something I developed many years ago and over the years I've had discussions with a few people who believe it or not really got upset about it because in their estimation it doesn't play by the rules.
0
 
Frank FreeseAuthor Commented:
Martin,
OK - great. I had to make a few changes to you code though. I moved CheckFormulaFunction and Exit Sub just before End Select and deleted the End If statement. It looks to have done the job.
0
 
Martin LissOlder than dirtCommented:
I see that I left an unneeded 'End If' at line 22 when I copied and modified buttersk's code.

I'd like to see your code because moving " CheckFormulaFunction and Exit Sub just before End Select" won't work if I understand what you are saying you did.
0
 
Frank FreeseAuthor Commented:
Here you go. I've go some "tweaking" on clearing:
Range("D5:F5").Value = "Error! Check you formula or category spelling"


Select Case True
    Case Range("C4").Text = "network" And Range("C5").Text = "7"
        Range("E4").Locked = False
        Range("E4").Font.Color = vbWhite
        Range("E4").Interior.Color = vbRed
        Range("E4").Value = "Correct"
    Case Range("C4").Text = "components" And Range("C5").Text = "19"
        Range("E4").Locked = False
        Range("E4").Font.Color = vbWhite
        Range("E4").Interior.Color = vbRed
        Range("E4").Value = "Correct"
    Case Else
        Range("D5:F5").Value = "Error! Check you formula or category spelling"
        Range("D5:F5").Font.Color = vbYellow
        Range("D5:F5").Interior.Color = vbBlack
        Range("D5:F5").Locked = True
        CheckFormulaFunction
        Exit Sub
End Select

Open in new window

0
 
Martin LissOlder than dirtCommented:
Okay, as long as you only want to do the CheckFormulaFunction procedure and Exit Sub when there's an error you should be fine.

BTW it looks like you can do this

Select Case True
    Case (Range("C4").Text = "network" And Range("C5").Text = "7") or (Range("C4").Text = "components" And Range("C5").Text = "19")
        Range("E4").Locked = False
        Range("E4").Font.Color = vbWhite
        Range("E4").Interior.Color = vbRed
        Range("E4").Value = "Correct"
    Case Else
        Range("D5:F5").Value = "Error! Check you formula or category spelling"
        Range("D5:F5").Font.Color = vbYellow
        Range("D5:F5").Interior.Color = vbBlack
        Range("D5:F5").Locked = True
        CheckFormulaFunction
        Exit Sub
End Select

Open in new window

0
 
Frank FreeseAuthor Commented:
thanks for all the options
I felt like I've learned a lot lately.
Good folks!
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now