(Solved) – Looping through a selected range in VBA


I need to go through a selected range of values (all in the same column) and find the average of every 3 values. For some reason, the line For Each cell In selectedRange.Cells doesn’t give me the full column that I’m looking for. To be more specific, I’m selecting the range to be from B14:B8773, so far it looks like it’s only giving me the range from B8161:B8773. Any help would be much appreciated, thank you.

Public Function findAvg(maxValue As Variant, preHour As Variant, sucHour As Variant, selectedRange As range)
Dim cell As range
Dim pValue As Variant
Dim cValue As Variant
Dim nValue As Variant
Dim avg As Variant
Dim cAvg As Variant

cAvg = (preHour   sucHour   maxValue) / 3
For Each cell In selectedRange.Cells
    If (cell.Value > 0) Then
    cValue = cell.Value
    Debug.Print cValue
    'pValue = cell.Offset(rowOffset:=-1, columnOffset:=-1).Value
    'Debug.Print pValue
    'nValue = cell.Offset(1, 0).Value
   ' avg = (pValue   nValue   cValue) / 3
    'Debug.Print avg
        'If avg > cAvg Then
        'cAvg = avg
       ' End If
    'Debug.Print pValue
    End If
    Next cell
    'findAvg = cAvg
End Function

Leave a Reply

Your email address will not be published. Required fields are marked *