[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

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
0
Frank Freese
Asked:
Frank Freese
  • 8
  • 7
1 Solution
 
Martin LissRetired ProgrammerCommented:
Don't you want to fill N7, 8. 9. 10 and 11?
0
 
Martin LissRetired ProgrammerCommented:
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
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.

 
Martin LissRetired ProgrammerCommented:
I see said the blind man, you have all O's in that sub rather than Q's.
0
 
Martin LissRetired ProgrammerCommented:
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
 
Martin LissRetired ProgrammerCommented:
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 LissRetired ProgrammerCommented:
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 LissRetired ProgrammerCommented:
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 LissRetired ProgrammerCommented:
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now