Hi, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
ffice
ffice" /><o
></o
>
<o
></o
>
I have a problem with a custom function which does not calculate correctly. This does not seem to be an issue with manual/automatic calculation settings, nor (I think) related to whether the function is specified as volatile. <o
></o
>
Situation is this:
<o
></o
>
In different cells the function refers to a slightly different inputs.
<o
></o
>
Problem is that if I drag the formula down over new cells, or if any input value changes then every cell that ahs the function in it is updated with the one (and only one) result. The fact that each cell should be calculating on a different range is not reflected.<o
></o
>
Any ideas where I’ve gone wrong??
If its a quick solution, great - if not, I'll try to upload screen caps of my s/sheet.
For what its worth, my code is below..
thanks in advance.
<o
></o
>
Function WaterSum(firstWell, Rates, WellCounts)<o
></o
>
<o
></o
>
n = ActiveCell.Column - firstWell.Column<o
></o
>
Set period = Range(firstWell, ActiveCell.Offset(0, -n))<o
></o
>
<o
></o
>
For Each wellset In period<o
></o
>
monthsOnline = ActiveCell.Row - wellset.Row + 1<o
></o
>
monthSum = wellset.Value * WorksheetFunction.VLookup(monthsOnline, Rates, 2)<o
></o
>
WaterSum = WaterSum + monthSum<o
></o
>
Next<o
></o
>
<o
></o
>
End Function<o
></o
>




<o


I have a problem with a custom function which does not calculate correctly. This does not seem to be an issue with manual/automatic calculation settings, nor (I think) related to whether the function is specified as volatile. <o


Situation is this:
<o


In different cells the function refers to a slightly different inputs.
<o


Problem is that if I drag the formula down over new cells, or if any input value changes then every cell that ahs the function in it is updated with the one (and only one) result. The fact that each cell should be calculating on a different range is not reflected.<o


Any ideas where I’ve gone wrong??
If its a quick solution, great - if not, I'll try to upload screen caps of my s/sheet.
For what its worth, my code is below..
thanks in advance.
<o


Function WaterSum(firstWell, Rates, WellCounts)<o


<o


n = ActiveCell.Column - firstWell.Column<o


Set period = Range(firstWell, ActiveCell.Offset(0, -n))<o


<o


For Each wellset In period<o


monthsOnline = ActiveCell.Row - wellset.Row + 1<o


monthSum = wellset.Value * WorksheetFunction.VLookup(monthsOnline, Rates, 2)<o


WaterSum = WaterSum + monthSum<o


Next<o


<o


End Function<o

