just adding a worksheet argument will not remove the need for
application.volatile.

The best way would be to add one or more Range argument that contain all the
cells referred to in your Range and Cells statements, then you would also
not need the Application.Caller and the UDF could refer to ranges on other
worksheets when needed.


Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com


"PBcorn" wrote in message
...
thanks for this. will test and feedback ASAP. if I added an additional
argument for the sheet instead, would this remove the need for
application.volatile?. Presumably then I could just qualify everything
with
worksheets("sht") where Function REFR(Product As Range, Colhead As Range,
Avars As String, sht as object). I would rather do this and edit existing
formulae than have application.volatile causing the cells to re-calc
everytime something changes.

"Charles Williams" wrote:

I see a couple of problems:

1. All your unqualified references to Cells and Range will default to
whatever the active sheet happens to be.
Presumably you really want to refer to the sheet that conatins the
formula
that calls the UDF.
This is Application.Caller.Parent

2. Since the UDF refers to ranges that are not in the argument list you
need
to make the UDF volatile, otherwise it will sometimes return the wrong
answer.

Try the code below (not tested)

Function REFR(Product As Range, Colhead As Range, Avars As String)

Dim c As Range
Dim cs As Integer
Dim p As Integer
Dim sumthese as variant
Dim ParentSheet as worksheet

Application.Volatile
REFR = "NF"

bFound = False
set ParentSheet=Application.Caller.Parent

For Each c In Colhead.Cells

If Trim(ParentSheet.Cells((c.Row - 1), (c.Column)).Value) = Trim(Avars)
And
Trim(c.Value) = Trim(Product) Then
sumthese = ParentSheet.Range(ParentSheet.Cells(c.Row + 1, c.Column),
ParentSheet.Cells(46, c.Column))
bFound = True
Exit For
End If

Next c

If bFound Then
REFR = sumthese
End If

End Function


Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"PBcorn" wrote in message
...
I have a UDF (see code below). I made a copy of the sheet in which the
UDF
is
used, and the UDF in the original sheet now seems to be looking up
values
in
the copy sheet.

Can someone help amend the UDF to correct this? From what i can see i
can:

1. Add a range argument (none currently exists for the range the UDF
operates on) But this would require editing a large number of formulae

2. Force the udf to always refer to the correct range no matter how
many
copies of sheets are made.


CODE:
Function REFR(Product As Range, Colhead As Range, Avars As String)

Dim c As Range
Dim cs As Integer
Dim p As Integer
Dim sumthese()

REFR = "NF"

bFound = False


For Each c In Colhead.Cells

If Trim(Cells((c.Row - 1), (c.Column)).Value) = Trim(Avars) And
Trim(c.Value) = Trim(Product) Then
sumthese() = Range(Cells(c.Row + 1, c.Column), Cells(46, c.Column))
bFound = True
Exit For
End If

Next c

If bFound Then
REFR = sumthese
End If

End Function