Why can't is the average function not working?

[jaimep]

Hi,

I am trying to get an average of a weeks worth of results.

This is what I have tried

Sub PaymentFunnelsAutomate2()
'
Sheets("DATA").Select
Range("a1").Select
Selection.End(xlToRight).Select

Dim c As Range
Set c = ActiveCell

Sheets("DATA").Select
Range("a3").Select
Selection.End(xlToRight).Select

Dim d As Range
Set d = ActiveCell

If c + 13 = d Then
Sheets("DATA").Select
Range("A1").Select
Selection.End(xlToRight).Select
Selection.Offset(0, 1).Select
ActiveCell = c + 7

'need to include the code for adding the weekly figures

Sheets("EM").Select
Range("A31").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, -6).Range("A1:G1").Select
ActiveCell.Activate

Dim e As Range
Set e = ActiveCell
Sheets("Weekly EM %").Select
Range("E31").Select
Selection.End(xlToRight).Select
Selection.Offset(0, 1).Select
ActiveCell = Average(EM!e)

End If

End Sub

But I get a compile error on the last line of code, the Average function

Compile error: Sub or function not defined

Can anybody help to clear up why and maybe offer a solution?

Thanks,
Jaime

 

[VoG]

Difficult to tell with all that selecting going on but maybe

ActiveCell.Value = WorksheetFunction.Average(Sheets("EM").Range(e.Address))

 

[jaimep]

Hi Peter,

Thanks for getting back to me.

Yeah, sorry about the messiness of the code.

I want to choose the last seven days worth of data, all from one line, and then take the average of this data.

This is what I used to select the last 7 days
Sheets("EM").Select
Range("A31").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, -6).Range("A1:G1").Select
ActiveCell.Activate

Dim e As Range
Set e = ActiveCell

I then wanted to go to a different sheet and use this reference (e) to get an average of it. Maybe there is a better way?

Sheets("Weekly EM %").Select
Range("E31").Select
Selection.End(xlToRight).Select
Selection.Offset(0, 1).Select
'ActiveCell.Value = WorksheetFunction.Average(Sheets("Weekly EM %").Range(e))
ActiveCell = Average(EM!e)

I tried to use the code that you sent accross but it said 'application defined or object defined error'.

Unfortunately, I am a bit of a novice so do not really understand this error. Is it something to do with the Address in (e.Address)?

Thanks,
Jaime

 

[VoG]

Before the line of code that errors try

MsgBox e.Address

Does that return the range reference that you are expecting?

 

[jaimep]

I have input that bit of code and it is giving me a MsgBox saying $GD$31 which is the first cell of the range that I have selected.

It then goes on to say
'Unable to get the Average of the WorksheetFunction class'.

 

[jaimep]

Actually!!!!

The code you wrote was perfect! I made a mistake when copying it in!!
THANKS ALOT!!!

 

[jaimep]

Hi VoG,

Maybe, one more question though.

When the macro writes down the value I was kind of expecting it to write it with the formula.

Is that possible?

 

[VoG]

Then maybe it should be

Set e = Selection

and

ActiveCell.Value = WorksheetFunction.Average(e)

 

[jaimep]

That works, but same result as before.

It is just writing the value rather than the formula in.

 

[VoG]

Try

ActiveCell.Formula = "=AVERAGE(EM!" & e.Address & ")"