Posted to microsoft.public.excel.worksheet.functions
|
|
Indirect Function() - summing across sheets
It is just a Boolean:
Dim doit as Boolean
after the other dim statement
--
Gary''s Student - gsnu2007g
"Richard" wrote:
In article
Gary''sStudent wrote:
Let's say that in A1 thru A3 we have:
RGB5
RGB9
B2
Then first install the following UDF:
Function addacross(r1 As Range, r2 As Range, r3 As Range) As Variant
Application.Volatile
Dim s1 As String, s2 As String, s3 As String
s1 = r1.Value
s2 = r2.Value
s3 = r3.Value
doit = False
For i = 1 To Sheets.Count
If Sheets(i).Name = s1 Then
doit = True
End If
If doit Then
addacross = addacross + Sheets(i).Range(s3).Value
End If
If Sheets(i).Name = s2 Then
doit = False
End If
Next
End Function
Next, in an unused cell, enter:
=addacross(A1,A2,A3)
This should give the sum of the B2's in sheets RGB5 thru RGB9.
UDFs are very easy to install and use:
1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window
If you save the workbook, the UDF will be saved with it.
To use the UDF from the normal Excel window, just enter it like a
normal Excel Function
To remove the UDF:
1. bring up the VBE window as above
2. clear the code out
3. close the VBE window
To learn more about UDFs, see:
http://www.cpearson.com/excel/Writin...ionsInVBA.aspx
Hi,
Thanks for that,
What's the reference to 'doit' in the VBA code?
I'm using a Mac at the moment which seems to complain about this - it
thinks it's an undeclared variable. However from memory I can't
recall that as a keyword from my PC VBA days.
I'll dig out an old PC shortly and try the UDF on that.
Many thanks
Richard
ï*‡ï*‡ïŸ¼
I'm using an evaluation license of nemo since 82 days.
You should really try it!
http://www.malcom-mac.com/nemo
|