Handling blank cells while calculating SMA

user_15622693 0
Viewed 59

I'm trying to skip the blank cells from a range so that it does not alter my simple moving average. So I try to start from a range, "copy/paste" the non blank values within a new range called oRng so that I can use the new range to calculate my SMA.

I got a error 5 saying "Argument or procedure call incorrect" on this line

Set oRng = Application.Union(oRng, cell)

Here is my full code:

Function SMA(rng As Range, N As Integer)

    Dim oRng As Range
    Dim cell As Range

    On Error GoTo ErrHandler

    For Each cell In rng
        If not IsEmpty(cell) = True Then
            Set oRng = Application.Union(oRng, cell)
    Next cell

    SMA = Application.Average(oRng.Resize(N, 1))

ErrHandler:
    Debug.Print Err.Description
End Function
union vba excel
2 Answers

oRng.Resize(N, 1) won't work with a non-continous range. You can only resize continous ranges. Therfore you need to resize the cells before you collect them in oRng using Union.

Also after Dim oRng As Range the variable oRng is Nothing and you cannot use Nothing in Union so you need to test this first and directly Set oRng = cell.Resize(N, 1) the first time and then use Union for the others:

Public Function SMA(ByVal rng As Range, ByVal N As Long) As Variant
    Dim oRng As Range

    On Error GoTo ErrHandler

    Dim cell As Range
    For Each cell In rng
        If Not IsEmpty(cell) Then
            If oRng Is Nothing Then
                Set oRng = cell.Resize(N, 1)
            Else
                Set oRng = Application.Union(oRng, cell.Resize(N, 1))
            End If
    Next cell

    SMA = Application.Average(oRng)

ErrHandler:
    Debug.Print Err.Description
End Function
user_32196130

Average Non-Contiguous (UDF or not)

enter image description here

  • Note that Average will fail if error values.

Columns

Function SMA_Columns( _
    ByVal FirstRowRange As Range, _
    Optional ByVal RowsCount As Long = 1) _
As Double

    Dim urg As Range ' Combined (Union) Range
    Dim crg As Range ' Column Range

    For Each crg In FirstRowRange.Resize(RowsCount).Columns
        If Not IsEmpty(crg.Cells(1)) Then
            If urg Is Nothing Then Set urg = crg Else Set urg = Union(urg, crg)
        End If
    Next crg
    If urg Is Nothing Then Exit Function
    
    SMA_Columns = Application.Average(urg)

End Function

Rows

Function SMA_Rows( _
    ByVal FirstColumnRange As Range, _
    Optional ByVal ColumnsCount As Long = 1) _
As Double

    Dim urg As Range ' Combined (Union) Range
    Dim rrg As Range ' Row Range

    For Each rrg In FirstColumnRange.Resize(, ColumnsCount).Rows
        If Not IsEmpty(rrg.Cells(1)) Then
            If urg Is Nothing Then Set urg = rrg Else Set urg = Union(urg, rrg)
        End If
    Next rrg
    If urg Is Nothing Then Exit Function
    
    SMA_Rows = Application.Average(urg)

End Function
user_98140690
Related