[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

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
0
Frank Freese
Asked:
Frank Freese
  • 10
  • 6
1 Solution
 
Martin LissRetired ProgrammerCommented:
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 LissRetired ProgrammerCommented:
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 LissRetired ProgrammerCommented:
Or change line 4 to

        Range("P7").Value = "Correct but please use the AVERAGE function"
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Frank FreeseAuthor Commented:
Have you tried these in the worksheet?
0
 
Martin LissRetired ProgrammerCommented:
Yes I did but apparently not enough. Back in a few mins.
0
 
Martin LissRetired ProgrammerCommented:
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 LissRetired ProgrammerCommented:
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 LissRetired ProgrammerCommented:
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 LissRetired ProgrammerCommented:
Oh wait I see:(
0
 
Martin LissRetired ProgrammerCommented:
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
 
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 LissRetired ProgrammerCommented:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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