[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel UDF with Range

Posted on 2011-05-11
2
Medium Priority
?
271 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:NewToVBA
2 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35738826
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

0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 35739472
Are you using that in a worksheet cell? If so, it should be rewritten so that the row and column checks take place in the formula before the function is called. Also, your With Sheets("MyPage") bit serves no purpose and should be removed.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question