asked on

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

Martin Liss

Don't you want to fill N7, 8. 9. 10 and 11?

Martin Liss

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 = ""

Frank Freese

ASKER

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).

Martin Liss

I see said the blind man, you have all O's in that sub rather than Q's.

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

BTW the data grid says "Calculate the AVERAGE in cell O7" and it should be P7.

Frank Freese

ASKER

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

Frank Freese

ASKER

thank for picking up the error in location to P7

Martin Liss

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.

Frank Freese

ASKER

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 ?

Martin Liss

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.

Frank Freese

ASKER

Hopefully, I'm getting closer to being done with that sheet - hopefully.

Frank Freese

ASKER

Hey, I didn't know that - thanks on the InStr(

Martin Liss

Hopefully, I'm getting closer to being done with that sheet - hopefully.

It looks nice.

Frank Freese

ASKER

thank you - I've tired (with you help)