VBA populates a cell

Folks,
In the attached workbook, the second worksheet labeled "AVERAGE" I have the following sub-routine when the worksheet activates:
 
Private Sub Worksheet_Activate()
With Worksheets("AVERAGE")
    .Range("O7") = 8
    .Range("O8") = 7
    .Range("O9") = 9
    .Range("O10") = 6
    .Range("O11") = 10
    .Range("P7").ClearContents
End With
ActiveSheet.Range("P7").Select
End Sub

Open in new window

Every value EXCEPT the one assigned to O7 is populated. I don't have this problem elsewhere
Excel-StatisticsRevised.xlsm
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:
Don't you want to fill N7, 8. 9. 10 and 11?
0
Martin LissOlder than dirtCommented:
I see that you shifted things to the right but now in the Worksheet_Change event you have

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo ErrorRoutine
Select Case True
    Case Range("P7").Value = ""
        ' The user hasn't given an answer so just clear any message that might be in P7
        Range("O7").Value = ""
0
Frank FreeseAuthor Commented:
No. Actually, I had to move everything over 1 column. O8:O11 work just fine and I don't see where I might have missed something someplace else. That's what baffles me (but sometime getting up in the morning baffles me).
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Martin LissOlder than dirtCommented:
I see said the blind man, you have all O's in that sub rather than Q's.
0
Martin LissOlder than dirtCommented:
The corrected sub
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo ErrorRoutine
Select Case True
    Case Range("P7").Value = ""
        ' The user hasn't given an answer so just clear any message that might be in P7
        Range("Q7").Value = ""
        ' We know at this point that there's something in O7 so see if it's the right answer
    Case WorksheetFunction.Average(Range("Q7:O11")) = Range("P7").Value
        ' Correct answer but see if he's used a formula
        If InStr(1, Range("P7").Formula, "average", vbTextCompare) > 0 Then
            Range("Q7").Value = "Correct"
        Else
            Range("Q7").Value = "Correct but please use the AVERAGE function"
        End If
   Case WorksheetFunction.Average(Range("Q7:O11")) <> Range("P7").Value
        ' Incorrect answer
        Range("Q7").Value = "Incorrect"
    Case Else
        Range("Q7").Value = "Incorrect"
End Select
Application.EnableEvents = True
Exit Sub
ErrorRoutine:
Range("P7").Value = Range("P7").Text & " Error - read Remarks!"
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
Martin LissOlder than dirtCommented:
BTW the data grid says "Calculate the AVERAGE in cell O7" and it should be P7.
0
Frank FreeseAuthor Commented:
Before I moved everything the range was N7:N11 - no problem. I don't understand the "Q's" - yet. Is the problem in the...forget it, you already answered my question. Everything is back to normal.
Thank you kindly, sir
0
Frank FreeseAuthor Commented:
thank for picking up the error in location to P7
0
Martin LissOlder than dirtCommented:
You're probably not finished with that sheet yet but I thought I should point out that the cell references in the example are also incorrect.
0
Frank FreeseAuthor Commented:
thank you ... I'll be back I promise. I'm trying to duplicate what was done in AVERAGES and apply it to AVERAGEA on checking values.
BTW,  in the above code did you make a provision is the average is a negative number? I don't see that. I would think that in line 11 it needs to be >0 or <0 ?
0
Martin LissOlder than dirtCommented:
Line 11 which is this

If InStr(1, Range("P7").Formula, "average", vbTextCompare) > 0 Then

isn't referring to any value on the sheet, rather the InStr function which says "starting in position 1, look at the formula in P7, and see if it contains the string "average". The last parameter says "ignore case". If found the function returns the starting position of the string, so if it's greater than 0 it's there and if it's not then 0 is returned.
0
Frank FreeseAuthor Commented:
Hopefully, I'm getting closer to being done with that sheet - hopefully.
0
Frank FreeseAuthor Commented:
Hey, I didn't know that - thanks on the InStr(
0
Martin LissOlder than dirtCommented:
Hopefully, I'm getting closer to being done with that sheet - hopefully.
It looks nice.
0
Frank FreeseAuthor Commented:
thank you - I've tired (with you help)
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.