How to write a average formula in excel vba

Question

I am trying to calculate average, but i am getting an run-time error. Here is my code..

lastrowcell = Range("B1", Range("B1").End(xlDown)).Rows.Count
Range("F1").Value = "High+Low/2"
For n = 2 To lastrowcell
    Range(Cells(n, 6)).Formula = "=average(" & Range(Cells(n, 2), Cells(n, 3)).Address(False, False) & ")"
Next

Can anyone show what I did wrong.

Thanks in advance...

No its not working... I am getting run-time error at Range(Cells(n, 6)).Formula = "=average(" & Range(Cells(n, 2), Cells(n, 3)).Address(False, False) & ")" — Sanjeev Shinde
– Sanjeev Shinde, Commented Apr 19, 2016 at 5:44

Dan Donoghue · Accepted Answer · 2016-04-19 05:47:00Z

3

You don't need to loop, Excel is smart enough to fill the entire thing in one go:

lastrowcell = Range("B1", Range("B1").End(xlDown)).Rows.Count
Range("F1").Value = "High+Low/2"
Range("F6:F" & LastRow).Formula = "=AVERAGE(B6:C6)"

The 6 will be incremented in each row

If you want the last row though, its always better to come from the bottom up unless you are specifically looking for the first blank:

lastrowcell = Range("B" & Rows.Count).end(xlup).row

answered Apr 19, 2016 at 5:47

Dan Donoghue

6,2662 gold badges20 silver badges36 bronze badges

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

1 Comment

Dan Donoghue Over a year ago

Glad I could help :)

BrakNicku · Accepted Answer · 2016-04-19 05:50:09Z

1

Range(Cells(n, 6))

is not correct syntax for Range property. When there is only one parameter, it should be string. Instead you can use:

Cells(n, 6)

or

Range("F" & n).

answered Apr 19, 2016 at 5:50

BrakNicku

6,0313 gold badges28 silver badges40 bronze badges

Collectives™ on Stack Overflow

How to write a average formula in excel vba

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related