Addtional IF problem

Folks,
In cell O7 the user is supposed to enter a function - AVERAGE". In cell P7 there's an IF statement that simply determines if an error was create look at the Remarks. If not then the answer is "Correct". However, here's my problem: The user can type in any value w/o using the AVERAGE function and the answer would be the same "Correct". I would like to force the user to enter in the AVERAGE function in cell O6 and not manually some value?
Modify IF
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.

Martin LissOlder than dirtCommented:
As I suggested in another thread I think you should start with this
=IF(O7 = AVERAGE(N7:N11),"Correct","Incorrect")

If you wanted to check if there was actually a formula in the cell you could use VBA to examine O7.Formula and if Instr finds "AVERAGE" then it's OK otherwise Msgbox "Please use a formula"
0
Martin LissOlder than dirtCommented:
And here's the code
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("P7").Value = "Correct" Then
    If InStr(1, Range("O7").Formula, "average", vbTextCompare) = 0 Then
        MsgBox "Please use the AVERAGE formula"
    End If
End If
End Sub

Open in new window

0
Martin LissOlder than dirtCommented:
Or change line 4 to

        Range("P7").Value = "Correct but please use the AVERAGE function"
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Frank FreeseAuthor Commented:
Have you tried these in the worksheet?
0
Martin LissOlder than dirtCommented:
Yes I did but apparently not enough. Back in a few mins.
0
Martin LissOlder than dirtCommented:
OK I think I've got it. Remove the formula from P7 and do this instead.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Select Case True
    Case Range("O7").Value = ""
        ' The user hasn't given an answer so just clear any message that might be in P7
        Range("P7").Value = ""
    Case WorksheetFunction.Average(Range("N7:N11")) = Range("O7").Value
        ' Correct answer but see if he's used a formula
        If InStr(1, Range("O7").Formula, "average", vbTextCompare) > 0 Then
            Range("P7").Value = "Correct"
        Else
            Range("P7").Value = "Correct put please use the AVERAGE function"
        End If
   Case WorksheetFunction.Average(Range("N7:N11")) <> Range("O7").Value
        ' Incorrect answer
        Range("P7").Value = "Incorrect"
    Case Else
        Range("P7").Value = "Incorrect"
End Select
Application.EnableEvents = True
End Sub

Open in new window

0
Frank FreeseAuthor Commented:
I tired to build a Case for when there is an error to appear in P7 such as:

IF(ISERROR(O7), "Error - See Remarks", IF(O7="", "", "Correct"))

Possible?
0
Martin LissOlder than dirtCommented:
Who knows but why go that route when my code works. Did you remove the formula and try it?
0
Frank FreeseAuthor Commented:
Here's why I ask with your new code:
error codeat
case
0
Martin LissOlder than dirtCommented:
Make sure the code you have matches what's in post Id 40191436 because I did make a change to it after posting and that you don't have a formula in P7 because...

Works for me
0
Martin LissOlder than dirtCommented:
Oh wait I see:(
0
Martin LissOlder than dirtCommented:
Attempt #47

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo ErrorRoutine
Select Case True
    Case Range("O7").Value = ""
        ' The user hasn't given an answer so just clear any message that might be in P7
        Range("P7").Value = ""
        ' We know at this point that there's something in O7 so see if it's the right answer
    Case WorksheetFunction.Average(Range("N7:N11")) = Range("O7").Value
        ' Correct answer but see if he's used a formula
        If InStr(1, Range("O7").Formula, "average", vbTextCompare) > 0 Then
            Range("P7").Value = "Correct"
        Else
            Range("P7").Value = "Correct but please use the AVERAGE function"
        End If
   Case WorksheetFunction.Average(Range("N7:N11")) <> Range("O7").Value
        ' Incorrect answer
        Range("P7").Value = "Incorrect"
    Case Else
        Range("P7").Value = "Incorrect"
End Select
Application.EnableEvents = True
Exit Sub
ErrorRoutine:
Range("P7").Value = Range("O7").Text & " put whatever error message you want here"
Application.EnableEvents = True

End Sub

Open in new window

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
Frank FreeseAuthor Commented:
There you go!
Attempt# 47 - I'm getting less picky.
Seriously, I'm grateful for your patience.
0
Frank FreeseAuthor Commented:
A fantastic job and thank you!
0
Martin LissOlder than dirtCommented:
YW
0
Frank FreeseAuthor Commented:
Stand-bye though and be thinking of THIS:
In the AVERAGE spreadsheet we were looking at only 1 cell to check. In the AVERAGEA spreadsheet we have multiple cells. For all worksheets with only one cell I'll duplicate what was done here. I'll be posting a question on Monday for multiple cells. Right now I have a different Excel project I'll be doing over the weekend. BTW you'd be surprised how much I have learned regarding VBA.

Have a safe weekend - I appreciate you.
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.

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.