My Function is not working

[ilya2004]

Hello all,

I am trying to develop a function that will give me the average of some numbers in a range that meet certain criteria. Here is the beginning of my code:

Function AverageBetween(Range As Range, LB As Double, UB As Double, Optional Criteria_Range As Range) As Long
Dim Mycel, SearchRange As Range
Dim n As Long
Dim i As Integer
i = 0

n = 0

Set SearchRange = Range.SpecialCells(xltypeformulas, xlNumbers)
For Each Mycel In SearchRange

If Mycel.Text >= LB And Mycel.Text <= UB Then
n = n + Mycel.Text
i = i + 1
End If
Next

averagebetween = n / i

Debug.Print n
Debug.Print i
Debug.Print averagebetween

End Function

It works fine in execution and I even get the correct answer in the immediate window, but when I call the function in a spreadsheet

=PERSONAL.XLSB!AvergeBetween(B49:B54,B50,B52)

for some reason I get "#name?" as the result in my cell.


Please help!!

 

[ilya2004]

I modified my code, here is an update:

Function AverageBetween(MyRange As Range, LB As Double, UB As Double, Optional Criteria_Range As Range)
Dim Mycel, SearchRange As Range
Dim n, i, Avg As Long
i = 0
n = 0



Set SearchRange = MyRange.SpecialCells(xlCellTypeFormulas, xlNumbers)
For Each Mycel In SearchRange
Debug.Print Mycel.Row
If Mycel.Text >= LB And Mycel.Text <= UB Then
n = n + Criteria_Range(Mycel.Row, 1)
i = i + 1
End If
Next



AverageBetween = n / i
Debug.Print n
Debug.Print i
Debug.Print Avg


End Function

 

[shg]

=PERSONAL.XLSB!Aver[COLOR=red]a[/COLOR]geBetween(B49:B54,B50,B52)

I wouldn't use a VBA keyword (Range) as a variable name.

 

[tusharm]

In addition to the typo that has already been pointed out to you, there are certain Excel functions/methods/properties that do not work in a user defined function. Maybe, SpecialCells is one of them.

You may wish to loop through each cell in the range and check if it contains a formula or not and if so then whether the value is a number or not.

I modified my code, here is an update:

Function AverageBetween(MyRange As Range, LB As Double, UB As Double, Optional Criteria_Range As Range)
Dim Mycel, SearchRange As Range
Dim n, i, Avg As Long
i = 0
n = 0



Set SearchRange = MyRange.SpecialCells(xlCellTypeFormulas, xlNumbers)
For Each Mycel In SearchRange
Debug.Print Mycel.Row
If Mycel.Text >= LB And Mycel.Text <= UB Then
n = n + Criteria_Range(Mycel.Row, 1)
i = i + 1
End If
Next



AverageBetween = n / i
Debug.Print n
Debug.Print i
Debug.Print Avg


End Function

 

[ilya2004]

I tried to remove the special cells and I fixed the typos and I am still getting zero as a result. I know that the issue is in the n's, for some reason it is not catching what the value is, so it leave it as zero.

Here is the full code:

    Function AverageBetween(MyRange As Range, _
    LB As Double, UB As Double, Optional Criteria_Range As Range)
    
    Dim Mycel, SearchRange As Range
    Dim n, i, Avg
    i = 0
    n = 0
    
       
    Set SearchRange = MyRange.SpecialCells(xlCellTypeFormulas, xlNumbers)
    
    For Each Mycel In SearchRange
    If Mycel.Text >= LB And Mycel.Text <= UB Then
    n = n + Criteria_Range.Cells(Mycel.Row, 1).Text
    i = i + 1
    End If
    Next
            
    AverageBetween = n / i
    
    End Function