VBA- Does not equal IF Statement

[brian662]

Hi I have the following code to either add, subtract, multiply or divide two numbers

Private Sub Cmd_Test_Click()
Worksheets("Sheet1").Range("C9") = "1st Value"
Worksheets("Sheet1").Range("D9") = "2nd Value"
Worksheets("Sheet1").Range("E9") = "Operation"
Worksheets("Sheet1").Range("F9") = "Result"


Dim sngMy1stValue As Single
Dim sngMy2ndValue As Single
Dim strMyOperation As String


sngMy1stValue = Worksheets("Sheet1").Range("C10").Value
sngMy2ndValue = Worksheets("Sheet1").Range("D10").Value
strMyOperation = Worksheets("Sheet1").Range("E10").Text


If strMyOperation = "+" Then
Worksheets("Sheet1").Range("F10").Value = sngMy1stValue + sngMy2ndValue
End If


If strMyOperation = "-" Then
Worksheets("Sheet1").Range("F10").Value = sngMy1stValue - sngMy2ndValue
End If


If strMyOperation = "*" Then
Worksheets("Sheet1").Range("F10").Value = sngMy1stValue * sngMy2ndValue
End If


If strMyOperation = "/" Then
Worksheets("Sheet1").Range("F10").Value = sngMy1stValue / sngMy2ndValue
End If


End Sub

I am trying to write an additional statement that sets the “Result” cell to 0 (zero) if a user does not enter one of the four basic mathematical operators, I know that in one area of the code, Value would equal 0, but I am not sure where to proceed with the strMyOperation = .......

Very grateful if you could help me out

 

[Jonmo1]

Welcome to the board..

Check out the Evaluate function,

all those IF's can be replaced with
Worksheets("Sheet1").Range("F10").Value = Evaluate("=" & sngMy1stValue & strMyOperation & sngMy2ndValue)


Then use select case to verify a valid operator was selected.

Try

Private Sub Cmd_Test_Click()
Worksheets("Sheet1").Range("C9") = "1st Value"
Worksheets("Sheet1").Range("D9") = "2nd Value"
Worksheets("Sheet1").Range("E9") = "Operation"
Worksheets("Sheet1").Range("F9") = "Result"

Dim sngMy1stValue As Single
Dim sngMy2ndValue As Single
Dim strMyOperation As String

sngMy1stValue = Worksheets("Sheet1").Range("C10").Value
sngMy2ndValue = Worksheets("Sheet1").Range("D10").Value
strMyOperation = Worksheets("Sheet1").Range("E10").Text
Select Case strMyOperation
    Case "+", "-", "*", "/"
        Worksheets("Sheet1").Range("F10").Value = Evaluate("=" & sngMy1stValue & strMyOperation & sngMy2ndValue)
    Case Else
        MsgBox "you didn't select a valid operator"
End Select
End Sub

 

[brian662]

The only problem is I need the result to equal 0 when a user does not enter a valid operator, although the code is good, it does not answer the question on how to change the result to 0 when a invalid operator is entered, could you please help on that?

 

[Jonmo1]

Replace this line

MsgBox "you didn't select a valid operator"

with

Worksheets("Sheet1").Range("F10").Value = 0

 

[brian662]

Thanks that is great

 

[Jonmo1]

glad to help, thanks for the feedback.

 

[brian662]

Can I just ask, is there anyway to perform this without using the SELECT CASE Block, and keeping the IF statements, and just adding another IF statement to set the result to zero when an invalid operator is entered?

 

[Jonmo1]

Try making it 1 If statement, and using ELSEIF

Private Sub Cmd_Test_Click()
Worksheets("Sheet1").Range("C9") = "1st Value"
Worksheets("Sheet1").Range("D9") = "2nd Value"
Worksheets("Sheet1").Range("E9") = "Operation"
Worksheets("Sheet1").Range("F9") = "Result"

Dim sngMy1stValue As Single
Dim sngMy2ndValue As Single
Dim strMyOperation As String

sngMy1stValue = Worksheets("Sheet1").Range("C10").Value
sngMy2ndValue = Worksheets("Sheet1").Range("D10").Value
strMyOperation = Worksheets("Sheet1").Range("E10").Text

If strMyOperation = "+" Then
    Worksheets("Sheet1").Range("F10").Value = sngMy1stValue + sngMy2ndValue
ElseIf strMyOperation = "-" Then
    Worksheets("Sheet1").Range("F10").Value = sngMy1stValue - sngMy2ndValue
ElseIf strMyOperation = "*" Then
    Worksheets("Sheet1").Range("F10").Value = sngMy1stValue * sngMy2ndValue
ElseIf strMyOperation = "/" Then
    Worksheets("Sheet1").Range("F10").Value = sngMy1stValue / sngMy2ndValue
Else
    Worksheets("Sheet1").Range("F10").Value = 0
End If
End Sub

 

[brian662]

Brilliant!!! That is all I needed to know, thanks