Excel formula to VBA function

Question

I need help to create a VBA function that does the same thing as this excel formula =AVERAGEIF(A2:A13,"<="&E2,B2:B13)

Welcome to Stack Overflow, glad to see a new member! Please check How to Ask and make your question a Minimal, Complete, and Verifiable example. — ForeverZer0
– ForeverZer0, Commented Jul 12, 2018 at 4:39
What have you tried? This shouldn't be too difficult to have a first stab at. Are the ranges always going to be fixed though? Or are they arguments you pass to your user defined function? — QHarr
– QHarr, Commented Jul 12, 2018 at 5:02

frog · Accepted Answer · 2018-07-12 06:40:31Z

0

The worksheetfunction.averageif method might be your answer.

For example:

WorksheetFunction.AverageIf(Range("A2:A13"),"<="&Range("E2"),Range("B2:B13"))

answered Jul 12, 2018 at 6:40

frog

265 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

RBK · Accepted Answer · 2018-07-12 06:55:03Z

0

Well, you can use like this,

WorksheetFunction Reference

Sub average()
MsgBox (WorksheetFunction.AverageIf(Worksheets(1).Range("A2:A13"), "<=" & Worksheets(1).Range("E1").Value, Worksheets(1).Range("B2:B13")))

'or like this to put in the range.

Worksheets(1).Range("F1").Formula = "=AVERAGEIF(A2:A13," >= "&E1,B2:B13)"
End Sub

edited Jul 12, 2018 at 6:55

answered Jul 12, 2018 at 6:45

RBK

1251 silver badge17 bronze badges

Collectives™ on Stack Overflow

Excel formula to VBA function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related