CODEWITHSUNDEEP
vbaexcel
Sanjeev Shinde
Sanjeev Shinde

Reputation: 61

How to write a average formula in excel vba

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...

Upvotes: 0

Views: 7965

Answers (2)

BrakNicku
BrakNicku

Reputation: 5990

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).

Upvotes: 1

Dan Donoghue
Dan Donoghue

Reputation: 6216

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

Upvotes: 3

Related Questions