Goto above average values.

[omairhe]

hello dear members,

how do I goto first target cell of a given range if the value of that cell is found to be greater than that
of an average value of the entire user specified range.

There are two data sets provided above with two figures each highlighted that are found above average of their respective ranges.
If my active row is currently 5 , I would like to jump to row 8 of the active column as this is the above average cell value.
So this will make my active row 8. Now if the macro is triggered again, I'd like to jump to row 9 as that value is the
next above average value. On reaching row 9 or row 10, I would like to stop here, as this is the last value of the data set and no more values are found to be
greater than average. hence triggering the macro on row 9 or 10 should do nothing.

Similarly if my active row is 13 , I would like to jump to row 17 and 19 of the active column in each vba calling.
and on reaching the last value of "500" the code should do nothing.

With my little vba expertise I was able to pull this much up through my previous post and some surfing on the net.

myAverageRng = Application.Average(Range(Cells(ActiveCell.Row, "C").End(xlUp), Cells(ActiveCell.Row, "C").End(xlDown))
Set LookUpRng = Range(Cells(ActiveCell.Row, "C"), Cells(ActiveCell.Row, "C").End(xlDown))
For each cells in LookUpRng , if the value is greater than myAverageRng then
do this:
Application.Goto Cells(LookUpRng.Row, ActiveCell.Column)
else:
'do nothing.

Will appreciate any help at all,
Much Thanks.

 

[omairhe]

I know this is a funny way of doing this. Could someone assist please?

Sub PassAverage()
Dim aRng As Range, nRng As Range, cell As Range
If Cells(ActiveCell.Row, "C").Value <> "" And Cells(ActiveCell.Row + 1, "C").Value <> "" Then
Set aRng = Range(Cells(ActiveCell.Row, "C").End(xlUp), Cells(ActiveCell.Row, "C").End(xlDown)) 'setting up average range
Set nRng = Range(Cells(ActiveCell.Row + 1, "C"), Cells(ActiveCell.Row, "C").End(xlDown)) 'setting up normal range
For Each cell In nRng
If cell.Value > Application.WorksheetFunction.Average(aRng) Then
Application.Goto Cells(cell.Row, ActiveCell.Column)
Exit Sub
End If
Next cell
End If
End Sub