How to stop UDF in Original ref'ng copied range
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 |
How to stop UDF in Original ref'ng copied range
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 |
How to stop UDF in Original ref'ng copied range
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 |
How to stop UDF in Original ref'ng copied range
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 |
How to stop UDF in Original ref'ng copied range
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" wrote: 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 |
How to stop UDF in Original ref'ng copied range
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 "PBcorn" wrote in message ... 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" wrote: 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 |
How to stop UDF in Original ref'ng copied range
Should have an Exit For before the EndIF
"Charles Williams" wrote in message ... 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 "PBcorn" wrote in message ... 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" wrote: 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 |
All times are GMT +1. The time now is 03:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com