Make sure you pass the range to the function.

Function myFunc(rng as range) as variant 'or as long or as ...

Then use:
=myfunc('sheet 99'!a1:b10)

If you don't pass the range(s) that the function needs, then it won't know when
to recalculate. Making the function volatile will mean that the function could
be one full calculation behind -- so the results should not be trusted until
that recalc.

Function myFunc(rng as range) as variant

dim myCell as range
dim mySum as double

mySum = 0

'some validation
if rng.areas.count 1 then
myfunc = "just a single area, please"
exit function
end if

if rng.columns.count 2 then
myfunc = "just two columns, please"
exit function
end if

'then you have a few options.
'you could loop through the cells in the first column
for each mycell in rng.columns(1)
if lcase(mycell.text) = lcase("hi there") then
if isnumeric(mycell.offset(0,1).value) then
mysum = mysum + mycell.offset(0,1).value
end if
end if
next mycell

myfunc = mysum

end function

(untested, uncompiled.)

You could also loop through the rows, then loop through the columns.

dim mycell as range
dim myrow as range
'same checks

for each myrow in rng.rows
for each mycell in myrow.cells
'do something
next mycell
next myrow








Greg wrote:

What is the best way to loop through a range argument in a User
Defined Function that is used n a worksheet cell when the range is a 2-
column range of cells and I want to do calculations with the numbers
by row for each pair of values? I need it to work in cases when the
worksheet is recalculated and the active sheet may be different from
the sheet that has the cell where the function is located.

Greg

--

Dave Peterson