How can I make an average of a range?-CodePudding

I want to make the average of the entries starting at B4. Tell me what should be changed in my code. Im new to vba.

    Range("F13").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=AVERAGE(Range(Range("B4").End(xlDown)))"

Edit: And how does it work in this case?

Range("F17").Select
    Application.CutCopyMode = False
    Range("F17").Formula = "=IF(COUNT(R[-13]C[-2]:R[-10]C[-2])=0,10^99,COUNT(" & Range(Range("D4"), Range("D4").End(xlDown)).Address & ")"

CodePudding user response：

If your goal is to have the actual formula in the cell (so it will automatically update if the values on the worksheet change) then you could do it like:

Sub test()
  Dim rgStart As Range, rgStop As Range, rg As Range
  Set rgStart = Range("B4")
  Set rgStop = rgStart.End(xlDown)
  Set rg = Range(rgStart, rgStop)
  Range("F13").Formula = "=AVERAGE(" & rg.Address & ")"
End Sub

...that is the tidier way to do it (more code but easier to understand).

This is the same code but more "compact":

Sub test()
  Range("F13").Formula = "=AVERAGE(" & Range(Range("B4"), Range("B4").End(xlDown)).Address & ")"
End Sub

If you don't actually want the formula in the cell (so it's a static value) you can still call the worksheet AVERAGE function with Application.WorksheetFunction, like:

Sub test()
  Dim rgStart As Range, rgStop As Range, rg As Range
  Set rgStart = Range("B4")
  Set rgStop = rgStart.End(xlDown)
  Set rg = Range(rgStart, rgStop)
  Range("F13") = Application.WorksheetFunction.Average(rg)
End Sub

...and again, "compacted":

Sub test()
  Range("F13") = Application.WorksheetFunction.Average(Range(Range("B4"), Range("B4").End(xlDown)))
End Sub