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

 

[TonyJollans]

Hi Henk,

I don't see why the one should work and the other not, but you are checking every sheet, including your aggregate sheet - does that also have a "Label" and do you want it included in your total?

Incidentally, you don't need the sh.Select line in the routine.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[