How to Find(...) in different worksheets with VBA?

[hstijnen]

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

 

[Bernie Deitrick]

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

 

[Dave Peterson]

I think it depends on what version of excel you're using.

If you're running xl2002 or higher, then .find seems to work nicely in functions
called from a worksheet UDF.

But I'd drop the sh.select and check to see if "Label" was found.
(xl2003 forgave the sh.select, though.)

Option Explicit
Function Total() As Double
Application.Volatile

Dim tot As Double
Dim sh As Worksheet
Dim rng As Range

tot = 0

For Each sh In ThisWorkbook.Sheets
Set rng = sh.Cells.Find("Label")
If rng Is Nothing Then
'do nothing
Else
tot = tot + rng.Offset(0, 1).Value
End If
Next
Total = tot
End Function

I added application.volatile, too. Since the function doesn't get any
parameters passed to it, it doesn't know when to recalculate.

Application.volatile means that it'll recalc whenever excel recalcs.