VBA syntax for AverageIF and AverageIFs - need help

[tableru]

Hi I am very new to VBA and have been trying to figure out how add formulas into Excel via VBA but been hitting the wall.

I have 2 Tabs,
Tab 1 = Multiple (2)
Tab 2 = Auto Created --> lets call it "Sheet1"

Sheet1:
Column A - is Date (15 min interval)
Column B - Lets call it "CPU"
Column C - Lets call it "Memory"
Column D - Lets call it "C:\"
4/17/2016 20:15 0.32 10.21 41.06
4/17/2016 20:30 0.38 10.2 41.06
4/17/2016 20:45 0.33 10.19 41.06
4/17/2016 21:00 0.4 10.195 41.06

Multiple (2) sheet:
Contains data of the similar nature but the Column A time is extracted data of anytime thus Sheet 1 is an average of 15 minutes of those lines.
Example:
4/17/2016 20:00 0.32 10.21 41.06
4/17/2016 20:05 0.32 10.21 0
4/17/2016 20:07 0 0 0
4/17/2016 20:10 0 0 41.06

Sheet1. B2 Formula contains =AVERAGEIF('Multiple (2)'!$A:$A,"<"&A2,'Multiple (2)'!B:B)
Sheet1. C2 Formula contains =AVERAGEIF('Multiple (2)'!$A:$A,"<"&A2,'Multiple (2)'!C:C)
Sheet1. D2 Formula contains =AVERAGEIF('Multiple (2)'!$A:$A,"<"&A2,'Multiple (2)'!D:D)
- condition is we I have more columns then the formula per column will be expanded for E2, F2, G2, ??2

I have multiple files which have different source names, thus 'Multiple (2)' is an any sheet name of a file. When I open the file, there is only 1 tab.

Sheet1. B3 Formula contains =AVERAGEIFS('Multiple (2)'!B:B,'Multiple (2)'!$A:$A,">="&A2,'Multiple (2)'!$A:$A,"<"&A3)
Sheet1. C3 Formula contains =AVERAGEIFS('Multiple (2)'!C:C,'Multiple (2)'!$A:$A,">="&A2,'Multiple (2)'!$A:$A,"<"&A3)
:
:
Sheet1. B4 Formula contains =AVERAGEIFS('Multiple (2)'!B:B,'Multiple (2)'!$A:$A,">="&A3,'Multiple (2)'!$A:$A,"<"&A4)
Sheet1. C4 Formula contains =AVERAGEIFS('Multiple (2)'!C:C,'Multiple (2)'!$A:$A,">="&A3,'Multiple (2)'!$A:$A,"<"&A4)
:
:

Question is how to I translate all these into VBA syntaxes?
I was trying with AverageIF and I gotten errors - appreciate advise:
Worksheets(myWS).Range("B2") = Application.WorksheetFunction.AverageIf(Worksheets(curWS).Range("A:A"), "<" & A2, Worksheets(curWS).Range("B:B"))

where myWS = Sheet1
currWS = any arbitrary tab, example 'Multiple (2)'

I believe my syntax used is incorrect thus leading not to be able to input the information correctly for AverageIF and AverageIFS. Appreciate your help.

 

[steve the fish]

Hi. You were close but you suddenly put A2 in the middle of your equation rather than Range("A2")

 

[steve the fish]

You may also wish to put the sheet name of that range like you have with the other ranges used eg Sheets(myWS).Range("A2")

 

[tableru]

Hi Steve,

Thanks. I made changes to the syntax:

Sheets(myWS).Range("A2") Worksheets(myWS).Range("B2") = Application.WorksheetFunction.AverageIf(Worksheets(curWS).Range("A:A"), "<" & Sheets(myWS).Range("A2") , Worksheets(curWS).Range("B:B"))

However now the error is different:
Run-Time Error'1004'
Unable to get the AverageIf property of WorksheetFunction class

What does that mean?

 

[tableru]

Sorry typo

Worksheets(myWS).Range("B2") = Application.WorksheetFunction.AverageIf(Worksheets(curWS).Range("A:A"), "<" & Sheets(myWS).Range("A2") , Worksheets(curWS).Range("B:B"))

 

[steve the fish]

Your syntax is now correct. It will probably mean that the averageifs errors. Its probably that there are no values in curWS column A that are lower in value that myWS cell A2. Or where there are values lower there is text in curWS column B. Do the averageifs formula in the worksheet. You should find it errors.

 

[tableru]

Hi Steve,

Found that to solve the error is to remove WorksheetFunction totally.
However the answer is returning #div/0 eventhough manually using the averageif formula it is giving me a value. I have manipulated the raw data to ensure I have values for this when I run the averageIF manually.

Worksheets(myWS).Range("B2") = Application.AverageIf(Worksheets(curWS).Range("A:A"), _
"" < "" & Sheets(myWS).Range("A3"), _
Worksheets(curWS).Range("B:B"))

Currently I am trying to test with "Worksheets(myWS).Range("B2").Value" or "Worksheets(myWS).Range("B2").Formula" or just "Worksheets(myWS).Range("B2")", I would still get #Div/0

I am not sure if the syntax below is correct
"" < "" & Sheets(myWS).Range("A3"),

though if we manually input the formula below it is
=AVERAGEIF(Multiple!$A:$A,"<"&$A3,Multiple!B:B)

Thus not sure if I am using the method correctly or not.

Any idea?

 

[steve the fish]

You don't need the "" < "" just "<"