How to stop UDF in Original ref'ng copied range

[PBcorn]

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

 

[Charles Williams]

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]

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]

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]

OK I have been trying to amend existing code to add in a range argument. No
luck yet - can you help? The problem is that i want the for -next to loop
through all the cells in the two header rows, and the code inside to refer to
the range below them.

Thanks

 

[Charles Williams]

Its hard to decipher what your data actually looks like, and excatly what
you are trying to do

If you have a table with 2 rows of headers (first row AVARS and second row
Product) followed by rows of data then something like this?

Function REFR(theTable As Range, Product As Range, Avars As String) as
variant
Dim vColHead As Variant
Dim k As Long
'
' default is #N/A
'
REFR = CVErr(xlErrNA)
'
' get headers
'
vColHead = theTable.Resize(2)
'
' search for the column
'
For k = 1 To UBound(vColHead, 2)
If Trim(vColHead(1, k)) = Trim(Avars) And Trim(vColHead(2, k)) =
Trim(Product) Then
'
' return the column of data
'
REFR = theTable.Resize(theTable.Rows.Count - 2, 1).Offset(2, k -
1)
End If
Next k
End Function

 

[Charles Williams]

Should have an Exit For before the EndIF

Its hard to decipher what your data actually looks like, and excatly what
you are trying to do

If you have a table with 2 rows of headers (first row AVARS and second row
Product) followed by rows of data then something like this?

Function REFR(theTable As Range, Product As Range, Avars As String) as
variant
Dim vColHead As Variant
Dim k As Long
'
' default is #N/A
'
REFR = CVErr(xlErrNA)
'
' get headers
'
vColHead = theTable.Resize(2)
'
' search for the column
'
For k = 1 To UBound(vColHead, 2)
If Trim(vColHead(1, k)) = Trim(Avars) And Trim(vColHead(2, k)) =
Trim(Product) Then
'
' return the column of data
'
REFR = theTable.Resize(theTable.Rows.Count - 2, 1).Offset(2,
k - 1)
End If
Next k
End Function