Henk,

As you have discovered, .Find doesn't work when called from a funtion. You
could tie your macro to the worksheet ccalculate event, and get pretty much
the same fucntionality, though without the 'formula' showing.

HTH,
Bernie
MS Excel MVP

"hstijnen" wrote in message
om...
Hi,

I've a workbook with different detail worksheets and one for
aggregating. In the aggregate sheet I want to compute a total of some
variable which exists on every detail sheet, but not in the same
address (row, col).

I've taken the following approach: I've devloped a Function Total() in
VBA. This function curses through all detail sheets, Find() the Label
of the variable and get the value with Offset. As follows:

Function Total() As Double
Dim tot As Double
tot = 0
Dim rng As Range
For Each sh In ThisWorkbook.Sheets
sh.Select
Set rng = sh.Cells.Find("Label")
tot = tot + rng.Offset(0, 1).Value
Next
Total = tot
End Function

Now the following Sub gives the correct answer:
Sub h1()
tot = Total
Worksheets("aggregate").Range("A1").Value = tot
End Sub

However, when I enter in sheet Aggregate in cell A1 the formula
"=Total()" and compute the workbook, the Label in function Total isn't
found and rng is Nothing!

What can be at hand?

Thanks for help,
Henk