VBA average selection on other sheet

[teatimecrumpet]

Hi,

I'm having trouble averaging a selection from another sheet and placing the answer in a different sheet. I want to set the selection of the range to be a variable name and then have the app take that range to run the calculation on. But my code will just return a zero. The code does work if I change meanrange to selection.



Sorry for the code I'm certainly going beyond my reach.

Sub datrang()
Dim foundOne As Range
Dim meanrange As Range
Dim answer As Integer
Dim startdate As String

On Error Resume Next
With ActiveWorkbook.Sheets("data")
Set foundOne = .Range("A:A").Find(What:=(startdate), after:=.Range("a1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If foundOne.Row > 1 Then
foundOne.Offset(0, 1).Select
Range(Selection, Selection.End(xlDown)).Select
'Range(foundOne, foundOne.End(xlDown)).Select
'Range(.Range("a1"), foundOne.Offset(-1, 0)).EntireRow.Delete shift:=xlUp
Set meanrange = Sheets("data").Range(Selection)
End If
End With

Sheets("calculations").Range("D2").Value = Application.WorksheetFunction.Average(meanrange)

On Error GoTo 0

End Sub

Thanks,

 

[VoG]

Try

Set meanrange = Selection