I have tried upteen different variations and cannot get the averageifs to work...
Anyone have an idea what I am doing wrong??


Dim OArng As Range 'Cell where Average is reported
Dim Arng As Range 'Range of Dates to Average
Dim Srng As Range 'Start Date
Dim Erng As Range 'End Date
Set OArng = Sheet1.Range("F3")
Set Srng = Sheet1.Range("C1")
Set Erng = Sheet1.Range("E1")
Set Arng = Sheet2.Range("D2:D" & Range("AJ" & Rows.Count).End(xlUp).Row)
With OArng
Application.WorksheetFunction.AverageIfs(Arng,Arng =Sheet1.Range("C1"),Arng<=Sheet1.Range("E1"))

"Hans Hamm" wrote:
Anyone have an idea what I am doing wrong??
Dim OArng As Range 'Cell where Average is reported
Dim Arng As Range 'Range of Dates to Average
Dim Srng As Range 'Start Date
Dim Erng As Range 'End Date
Set OArng = Sheet1.Range("F3")
Set Srng = Sheet1.Range("C1")
Set Erng = Sheet1.Range("E1")
Set Arng = Sheet2.Range("D2:D" & Range("AJ" & Rows.Count).End(xlUp).Row)
With OArng
Application.WorksheetFunction.AverageIfs(Arng,
Arng=Sheet1.Range("C1"),Arng<=Sheet1.Range("E1"))

The form Arng=Sheet1.Range("C1") is not even valid in Excel.

In Excel, we would write (assume that Arng is a named range):

AVERAGEIFS(Arng,Arng,"=" & C1,Arng,"<=" & E1)

Likewise in VBA, we would write:

WorksheetFunction.AverageIfs(Arng,Arng,"=" & Range("C1"),Arng,"<=" &
Range("E1"))

Errata (typos).... I wrote:
"Hans Hamm" wrote:
Set Srng = Sheet1.Range("C1")
Set Erng = Sheet1.Range("E1")
Set Arng = Sheet2.Range("D2:D" & Range("AJ" & Rows.Count).End(xlUp).Row)
[....]
WorksheetFunction.AverageIfs(Arng,Arng,"=" & Range("C1"),
Arng,"<=" & Range("E1"))

Of course, the Range references should be Sheet1.Range references. But
since you set Srng and Erng, we should use them, to wit:

WorksheetFunction.AverageIfs(Arng,Arng,"=" & Srng,Arng,"<=" & Erng)

On Friday, October 26, 2012 5:55:16 PM UTC-4, joeu2004 wrote:
Errata (typos).... I wrote:

"wrote:

Set Srng = Sheet1.Range("C1")

Set Erng = Sheet1.Range("E1")

Set Arng = Sheet2.Range("D2:D" & Range("AJ" & Rows.Count).End(xlUp).Row)

[....]

WorksheetFunction.AverageIfs(Arng,Arng,"=" & Range("C1"),

Arng,"<=" & Range("E1"))



Of course, the Range references should be Sheet1.Range references. But

since you set Srng and Erng, we should use them, to wit:



WorksheetFunction.AverageIfs(Arng,Arng,"=" & Srng,Arng,"<=" & Erng)

Joe,
I just tried that one...
Dim OArng As Range 'Cell Where Average is reported
Dim Arng As Range 'Range of Dates to Average
Dim Srng As Range 'Start Date
Dim Erng As Range 'End Date

Set OArng = Sheet1.Range("F3")
Set Arng = Sheet2.Range("D2:D" & Range("AJ" & Rows.Count).End(xlUp).Row)
Set Srng = Sheet1.Range("C1")
Set Erng = Sheet1.Range("E1")

With OArng
Application.WorksheetFunction.AverageIfs(Arng,Arng ,"="&Srng,Arng,"<="&Erng)
End With

And I get an error stating; Compile error Expected:=
Any ideas? Thanks!

hi,

try replacing:

Set Arng = Sheet2.Range("D2:D" & Range("AJ" & Rows.Count).End(xlUp).Row)

by

Set Arng = Sheet2.Range("D2:D" & Sheet2.Range("AJ" & Rows.Count).End(xlUp).Row)


--
isabelle


Le 2012-10-26 18:32, Hans Hamm a écrit :
On Friday, October 26, 2012 5:55:16 PM UTC-4, joeu2004 wrote:
Errata (typos).... I wrote:

"wrote:
Set Srng = Sheet1.Range("C1")
Set Erng = Sheet1.Range("E1")
Set Arng = Sheet2.Range("D2:D" & Range("AJ" & Rows.Count).End(xlUp).Row)
[....]

WorksheetFunction.AverageIfs(Arng,Arng,"=" & Range("C1"),
Arng,"<=" & Range("E1"))


Of course, the Range references should be Sheet1.Range references. But

since you set Srng and Erng, we should use them, to wit:



WorksheetFunction.AverageIfs(Arng,Arng,"=" & Srng,Arng,"<=" & Erng)
Joe,
I just tried that one...
Dim OArng As Range 'Cell Where Average is reported
Dim Arng As Range 'Range of Dates to Average
Dim Srng As Range 'Start Date
Dim Erng As Range 'End Date

Set OArng = Sheet1.Range("F3")
Set Arng = Sheet2.Range("D2:D" & Range("AJ" & Rows.Count).End(xlUp).Row)
Set Srng = Sheet1.Range("C1")
Set Erng = Sheet1.Range("E1")

With OArng
Application.WorksheetFunction.AverageIfs(Arng,Arng ,"="&Srng,Arng,"<="&Erng)
End With

And I get an error stating; Compile error Expected:=
Any ideas? Thanks!

"Hans Hamm" wrote:
Joe,I just tried that one...
Dim OArng As Range 'Cell Where Average is reported
Dim Arng As Range 'Range of Dates to Average
Dim Srng As Range 'Start Date
Dim Erng As Range 'End Date
Set OArng = Sheet1.Range("F3")
Set Arng = Sheet2.Range("D2:D" & Range("AJ" & Rows.Count).End(xlUp).Row)
Set Srng = Sheet1.Range("C1")
Set Erng = Sheet1.Range("E1")
With OArng
Application.WorksheetFunction.AverageIfs(Arng,Arng ,"="&Srng,Arng,"<="&Erng)
End With
And I get an error stating; Compile error Expected:=

I neglected to correct a syntax error in your original posting. Of course,
the result of AverageIfs should be assigned to something. For example:

Dim x As Double
x = Application.WorksheetFunction.AverageIfs(Arng,Arng ,"=" & Srng,Arng,"<="
& Erng)

Also note the spaces around the ampersands (&). VBA seems to correct for
their absence in __this__ context. But in general, they are required; and
VBA does not always make the intended correction. So it would behoove you
to get into the habit of entering the interstitial spaces.

-----

Unrelated observations....


1. The With statement is unnecessary in this context. Alternatively, the
AverageIfs expression is not doing what you intended. As written,
AverageIfs uses ranges that are in column D of Sheet1, not column F (OArng).

But perhaps the With statement is part of a large context.


2. Range("AJ" & Rows.Count).End(xlUp).Row is suspicious. As written, it
looks at column AJ of ActiveSheet, which might be neither Sheet1 nor Sheet2.
But it is using that value to determine the end of column D in Sheet2.

If that is you intention, there is no problem.

But perhaps you want to write either Sheet2.Range("AJ" &
Rows.Count).End(xlUp).Row or the following:

I will look at your points and observations and see if it will work... will let you know,thank you very much!