If (or case?) help

Folks,
I have a command button that will check the values in a specific range to make sure they are correct. The cell are formatted number with 2 decimal places.
The range is from D7:D14 and cell D15.
If the value is any cell does not match the answer, I'd like to tell the user that the answer is incorrect and to check their formula or function. For example, the answer for D7 maybe 13.23 but the user got 565.
The only cells not protected will be those that have formulas or functions. I'm not sure a Case or If statement is best to use and what the code would lokk like.
Frank FreeseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steven HarrisPresidentCommented:
Do you have a sample layout?
0
Frank FreeseAuthor Commented:
Before we go there let me run this by you.
I created a module called CheckFormulaFunction as follows:
Sub CheckFormulaFunction()
MsgBox ("Your formula or function did not generate the correct answer."), vbOKOnly, "Incorrect"
End Sub

Open in new window


Then I created a command button to check the answer. Here's that code:
Private Sub cmdCkAnserAverageCon_Click()
If D7 <> 13.23 Then
    CheckFormulaFunction
    End If
    Else
    If D8 <> 15.35 Then
    CheckFormulaFunction
    End If
 End Sub

Open in new window


This "appears" to work, but is it right or is there a better way?
My mistake. This is not working past the first IF

That's not working either. You see D7 is a formula. So how do I resolve this
0
Steven HarrisPresidentCommented:
For starters, you need to remove the ELSE.

Next, how are you telling them which cell is wrong?  With this method, you will just get 2 popup warnings but they don't tell you which ones are wrong.
0
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Steven HarrisPresidentCommented:
Now, if you don't mind having to populate these before hand, you can go with:

Private Sub cmdCkAnserAverageCon_Click()
    If D7 <> 13.23 Then
        MsgBox ("Your formula or function in cell D7 did not generate the correct answer."), vbOKOnly, "Incorrect"
    End If

    If D8 <> 15.35 Then
        MsgBox ("Your formula or function in cell D8 did not generate the correct answer."), vbOKOnly, "Incorrect"
    End If
End Sub

Open in new window

0
Frank FreeseAuthor Commented:
OK - but the value returned from the formula is <>13,23 in reality. It might be 13.2345657 but the cell format is set to 2 decimal places. So you only see 13.23.
I'm consider defining a variable type to a number restricted to 2 decimal places but I can't find out how to do that. From there I can take the value returned to the 2 decimal places and comapre that with a coded 13.23. Can you help me there?
0
Steven HarrisPresidentCommented:
Aye!  Always check your code!  I can't believe I let this slip...

Private Sub cmdCkAnserAverageCon_Click()
    If Range("D7") <> "13.23" Then
        MsgBox ("Your formula or function in cell D7 did not generate the correct answer."), vbOKOnly, "Incorrect"
    End If

    If Range("D8") <> "15.35" Then
        MsgBox ("Your formula or function in cell D8 did not generate the correct answer."), vbOKOnly, "Incorrect"
    End If
End Sub

Open in new window


A cell reference means nothing unless you tell VBA it is a cell using Range("cell")

With that said, I am still looking at a solution for the visible versus actual values.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steven HarrisPresidentCommented:
Alright, so a little explanation first before the suggested solution:

When working in VBA, we cannot specify a cell or range as easily as we do in formulas as I mentioned in my last post.  With this in mind, we have to change D7 to Range("D7") and so on.  The value you are looking for is OK by itself, but I prefer to use quotes around them to prevent any errors if you need to reuse the code for another purpose later on.  Next, we need to look at the final formatted value that is displayed by Excel, not the raw value using .Text.  As a last step, I added a message box to let you know the macro is done checking.

Private Sub cmdCkAnserAverageCon_Click()
    If Range("D7").Text <> "13.23" Then
        MsgBox ("Your formula or function in cell D7 did not generate the correct answer."), vbOKOnly, "Incorrect"
    End If

    If Range("D8").Text <> "15.35" Then
        MsgBox ("Your formula or function in cell D8 did not generate the correct answer."), vbOKOnly, "Incorrect"
    End If
    
    MsgBox ("Validation Complete!")
End Sub

Open in new window


Now, using your example of "13.2345657", change the value to "13.2365657" and the macro will prompt to check your cell.
0
Frank FreeseAuthor Commented:
thanks - I caught myself trying to use the range at one time but failed. I can see why now.
Appreciate it
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.