Search code examples
vbaaveragearray-difference

Calculate the difference of every registered value and the average of every 5th registered value with VBA

I continuously need to evaluate sets of raw data (1-1000 rows, 3 columns) in 5-15 sheets every time.

For two of the columns I have written a code that helps me take the average of every 5th value (every 5th row) adjusted to the number of rows by a reoccurring text value at the bottom. I want to calculate the residual of every raw value, in steps of 5, to the average within that range.

This is a screen shot out of the data set and the average calculation

It would be easy to calculate the residual for every row if the average was printed out on every row, and then do the residual calculation, but I can't figure out how and that is what I need help with.

Here is my code so far

Dim i As Integer

rownum = Range(ToCellB.Address).Row 'This is a reference to cell at the bottom at which the average function should end

For i = 23 To rownum Step 5
  ActiveSheet.Range("L" & i).Value = _
   (ActiveSheet.Range("B" & i).Value + _
    ActiveSheet.Range("B" & i + 1).Value + _
    ActiveSheet.Range("B" & i + 2).Value + _
    ActiveSheet.Range("B" & i + 3).Value + _
    ActiveSheet.Range("B" & i + 4).Value) / 5

  ActiveSheet.Range("M" & i).Value = _
   (ActiveSheet.Range("G" & i).Value + _
    ActiveSheet.Range("G" & i + 1).Value + _
    ActiveSheet.Range("G" & i + 2).Value + _
    ActiveSheet.Range("G" & i + 3).Value + _
    ActiveSheet.Range("G" & i + 4).Value) / 5

Next i
Solution

  • The Range object can contain more than one cell, and if this is the case, assigning a value to it assigns the value to the whole range.

    Use

    Dim i As Integer

rownum = Range(ToCellB.Address).Row

For i = 23 To rownum Step 5
  ActiveSheet.Range("L" & i & ":L" & i + 4).Value = _
   (ActiveSheet.Range("B" & i).Value + _
    ActiveSheet.Range("B" & i + 1).Value + _
    ActiveSheet.Range("B" & i + 2).Value + _
    ActiveSheet.Range("B" & i + 3).Value + _
    ActiveSheet.Range("B" & i + 4).Value) / 5

  ActiveSheet.Range("M" & i & ":M" & i + 4).Value = _
   (ActiveSheet.Range("G" & i).Value + _
    ActiveSheet.Range("G" & i + 1).Value + _
    ActiveSheet.Range("G" & i + 2).Value + _
    ActiveSheet.Range("G" & i + 3).Value + _
    ActiveSheet.Range("G" & i + 4).Value) / 5

Next i

    instead.

    This create a range Range("L23:L27") for example, and then the entire range is populated with the local average.

    Also, a call to the value property is implicit in VBA:

    The default member of Range forwards calls without parameters to Value. Thus, someRange = someOtherRange is equivalent to someRange.Value = someOtherRange.Value.

    and can be dropped.

    And ToCellB is already a range, and so you can just write:

    Dim i As Integer

rownum = ToCellB.Row

For i = 23 To rownum Step 5
  ActiveSheet.Range("L" & i & ":L" & i + 4) = _
   (ActiveSheet.Range("B" & i) + _
    ActiveSheet.Range("B" & i + 1) + _
    ActiveSheet.Range("B" & i + 2) + _
    ActiveSheet.Range("B" & i + 3) + _
    ActiveSheet.Range("B" & i + 4)) / 5

  ActiveSheet.Range("M" & i & ":M" & i + 4) = _
   (ActiveSheet.Range("G" & i) + _
    ActiveSheet.Range("G" & i + 1) + _
    ActiveSheet.Range("G" & i + 2) + _
    ActiveSheet.Range("G" & i + 3) + _
    ActiveSheet.Range("G" & i + 4)) / 5

Next i

    Ref: