Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Find(...) in different worksheets with VBA?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Find(...) in different worksheets with VBA?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Find(...) in different worksheets with VBA?
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. hstijnen wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in more than 100 worksheets at once | Excel Worksheet Functions | |||
Find across multiple worksheets | Excel Worksheet Functions | |||
Find sum across all worksheets | Excel Worksheet Functions | |||
Find Discrepancies between Worksheets | Excel Discussion (Misc queries) | |||
How can you find how many worksheets in a workbook? | Excel Discussion (Misc queries) |