NewToVBA
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The activecell is supposed to be the cell where the cursor currently is.
Try this modification
Open in new window