Custom function not working - Excel 2007

[teeco]

Hi, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><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>

 

[mikerickson]

What is the formula that is being dragged?
By using ActiveCell, is your intent to return the cell that holds the formula?

 

[teeco]

I just have the function itself as the formula,

for example,
cell G6 has "=WaterSum($F$3,$A$3:$C$602,$F$3:F6)"
whereas cell G7 has "=WaterSum($F$3,$A$3:$C$602,$F$3:F7)"

The formula tries to use my function to calculate a result based on a slightly different input range (F3:F6 compared to F3:F7 in the example cells above)

I used activecell so that the input variable ($F3:F6 in above example) is set correctly, regardless of which column in the spreadsheet I invoke that function. There are probably much better ways to achieve that nut I'm not the expert I would like to be...

cheers

 

[mikerickson]

The wellcounts argument is the only one that is different between those two formulas. And the UDF doesn't do anything with wellcounts, so the results are the same.

 

[teeco]

thanks Mike - you're spot on.
I'll go back and sort it out,

cheers