How to average visible/filtered cells in a range meeting a criteria from another range in VBA Function?

Question

I´m pretty new at this so I ´ve been having some trouble with this code and was hoping to get some help. The goal is to average a range of filtered/visible cells when it meets the criteria of another range2 (which is text).

I have the following so far:

Function AverVisibleIC(Rg As Range, BU As Range)

Dim xCell As Range
Dim xCount As Integer
Dim xTtl As Double
Dim c As Range
Set BU = Range("B13:B44")

Application.Volatile
Set Rg = Intersect(Rg.Parent.UsedRange, Rg)
For Each xCell In Rg
    If xCell.ColumnWidth > 0 _
      And xCell.RowHeight > 0 _
      And Not IsEmpty(xCell) _
      And IsNumeric(xCell.Value) Then
          xTtl = xTtl + xCell.Value
          xCount = xCount + 1
    End If
Next
    If xCount > 0 Then
        For Each c In BU
        If c.Value = "IC" Then
            AverVisibleIC = xTtl / xCount
             Else
                AverVisibleIC = 0
     End If
         End If
Next
End Function

Thanks!

Does the used range have both rows and columns that are zero width/height? — , Jul 06 '18 at 18:55
Why do you pass in BU as a range argument and then reset it to `Set BU = Range("B13:B44")` without ever using it? — , Jul 06 '18 at 18:56

score 0 · Accepted Answer · 2018-07-09T20:06:36.787

0

Reset the BU range according to the size remaining of Rg after intersecting it with the used range.

You seem to only know if IC is within BU once. You original loop may have overwritten the desired result by continuing to look for IC after IC was found.

I don't know if this is perfect but perhaps it is closer to what you are trying to achieve.

Function AverVisibleIC(Rg As Range, BU As Range)

    Dim xCount As long, xTtl As Double, i as long

    Application.Volatile

    Set Rg = Intersect(Rg.Parent.UsedRange, Rg)
    set bu = bu.cells(1).resize(rg.rows.count, rg.columns.count)

    For i=1 to Rg.cells.count
        If Rg.cells(i).ColumnWidth > 0 _
          And Rg.cells(i).RowHeight > 0 _
          And Not IsEmpty(Rg.cells(i)) _
          And IsNumeric(Rg.cells(i).Value) _
          and cbool(instr(1, bu.cells(i).value2, "IC", vbtextcompare)) Then
              xTtl = xTtl + Rg.cells(i).Value2
              xCount = xCount + 1
        End If
    Next i

    If xCount > 0 Then
         AverVisibleIC = xTtl / xCount
    End If

End Function

edited Jul 09 '18 at 20:06

answered Jul 06 '18 at 19:05

It helped, it is averaging only visible cells but without takng into account "IC". The objective is that when I filter for "IC" in the BU range, it wil give me an average of the other rng without considereing the "hidden cells/not IC cells". – Juan Pablo Alfonso Santos Jul 06 '18 at 19:53
OK, I think I have a better idea what you are trying to achieve. BU should be checked for every cell in Rg. – Jul 06 '18 at 19:58
Yes, exactly. Now i have I tried running that function but it gives a "Compile Error: Invalid Next control variable reference" and it highlights the "Next xCell". – Juan Pablo Alfonso Santos Jul 07 '18 at 04:29
My bad. Yes, I made an error in the code. The fix is to swap 4 characters for a single other character. Can you use common debugging tools to determine me error? – Jul 07 '18 at 04:49
Okay sorry i´m getting pretty lost here, which characters? And not really for the debugging part, and I guess it is not letting me enter debugging since it is a Function? sorry again im just starting to learn.. – Juan Pablo Alfonso Santos Jul 07 '18 at 23:52
I got it to work now, I just had to change the "Next xCell" for "Next i" and select the correct range, thank you very much, your help was greatly useful. – Juan Pablo Alfonso Santos Jul 09 '18 at 19:47

How to average visible/filtered cells in a range meeting a criteria from another range in VBA Function?

1 Answers1