Link to home
Start Free TrialLog in
Avatar of NewToVBA
NewToVBAFlag for United States of America

asked on

Excel UDF with Range

Hi Experts - my function returns and errors, which the error checker describes as
1 - circular reference
2. "the value used in the formula is of a wrong data type".

I will appreciate your advise where the problem is.
When I debugged I noticed that the loop successfully does what I wanted, it goes through an entire range specificed in the input "inRange", but then it does it again instead of ending on the last cell.

Cheers, V

....
Function Test(InRange)
    With Sheets("MyPage")
    x = 0
    y = 0
If 4 < ActiveCell.Row <= 140 And ActiveCell.Column = 16 And Cells(ActiveCell.Row, 3) <> "" Then
For Each aCell In InRange
    If aCell > Cells(3, aCell.Column) Then
        x = x + Cells(3, aCell.Column).Value
    Else
        x = x + aCell.Value
End If
        y = y + Cells(3, aCell.Column).Value
Next aCell
ActiveCell = x / y
End If
End With
End Function
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

I am not sure what your function is trying to do but it appears that the reason for the circular reference is the use of activecell.

The activecell is supposed to be the cell where the cursor currently is.

Try this modification
Function cTest(InRange)
Set Ac = Application.Caller
With Sheets("MyPage")
    x = 0
    y = 0
    If 4 < Ac.Row And Ac.Row <= 140 And Ac.Column = 16 And Cells(Ac.Row, 3) <> "" Then
        For Each aCell In InRange
            If aCell > Cells(3, aCell.Column) Then
                x = x + Cells(3, aCell.Column).Value
            Else
                x = x + aCell.Value
            End If
            y = y + Cells(3, aCell.Column).Value
        Next aCell
        cTest = x / y
    End If
End With
End Function

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial