Creating a Macros function but co9ntent of function only works in a sub plz help

[Alexandre Brisebois (www.pointnetsolutions.com)]

Hey, I am creating a function that need to go read off of different
sheets andsum up a specific cell on all active sheets defined on the
first summary sheet.

the problem is that the code works great in a sub since i have to move
from sheet to sheet but when i get to a function it simply does not
work any more...

to find the cell i need to sum i have to locate specific content of a
cell in a cpecific column in this case the column is A & i must look
for the content of the cells then offset from it and sum the value on
all th esheets specified on the frist sheet where the names of the
sheets are available.


Any ideas?


Code --->>>

Public Function SumTotal(ByRef cellref As Object, offsetBy As Integer)
On Error Resume Next

Dim accumulator As Double

Dim start As String
start = ActiveSheet.Name

Dim InRange As Range

'get the active sheet names.
Set InRange = Intersect(ActiveSheet.UsedRange, Columns("AX:AX"))

Dim Rng As Range
Dim tempRng As Range

'Application.Volatile True

'sum all sheet values
For Each Rng In InRange.Cells
If Not (IsEmpty(Rng)) Then
Sheets(Rng.Value).Select
Range("A1").Select
Cells.Find(What:=cellref.Value, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Offset(rowOffset:=0,
columnOffset:=offsetBy).Activate
accumulator = accumulator + ActiveCell.Value
End If
Next Rng

Sheets(start).Select
SumTotal = accumulator
End Function

 

[Dave Peterson]

First, what version of excel are you using?

xl2002 was the first version to allow you to use .find in a UDF called from a
worksheet.

And you could get rid of all the .selects and activates. And add that
"application.volatile" line back in. You're not passing enough to the function
to keep track if things change in those other worksheets.

So that means that this function could be one calculation behind being accurate.

This did work ok in xl2003:

Option Explicit

Public Function SumTotal(ByRef cellref As Range, offsetBy As Long)

Application.Volatile

Dim accumulator As Double
Dim testWks As Worksheet
Dim Rng As Range
Dim tempRng As Range
Dim InRange As Range
Dim FoundCell As Range

'get the active sheet names.
With Application.Caller.Parent
Set InRange = Intersect(.UsedRange, .Columns("AX:AX"))
End With

'sum all sheet values
For Each Rng In InRange.Cells
If Not (IsEmpty(Rng)) Then
Set testWks = Nothing
On Error Resume Next
Set testWks = Worksheets(Rng.Value)
On Error GoTo 0
If testWks Is Nothing Then
'not a good worksheet name
Else
With testWks
Set FoundCell = .Cells.Find(What:=cellref.Value, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
'do nothing
Else
With FoundCell.Offset(0, offsetBy)
If IsNumeric(.Value) Then
accumulator = accumulator + .Value
End If
End With
End If
End With
End If
End If
Next Rng
SumTotal = accumulator
End Function

 

[Alexandre Brisebois (www.pointnetsolutions.com)]

Hey Dave,
I am working on excel 2000,

I am limited to its capabilities and simply do not know how i should go
about doing this,

like you can see i grab the names of the sheets. then i cycle through
the names go to the sheets and find the cell containing the value of
the cell i am passing by reference. then i need to offset, get the
value accumulate the value

and move to the next sheet and start ofver again...

i might have something in the wrong order or not using the right
technique.

Regards,
Alexandre

 

[Dave Peterson]

If the value that you're looking for is always in the same column of all those
worksheets, you could use application.match to look for it.

If the column varies between worksheets, then you could cycle through all the
columns of each worksheet and look for it (using application.match in a loop).

 

[Alexandre Brisebois (www.pointnetsolutions.com)]

do you have an example of the appluication match ?

i decided to do this using a sub afterall but i am having some
problems..

 

[Dave Peterson]

Is it just one column or multiple columns?

I'm gonna guess just one column:

Option Explicit
Public Function SumTotal(ByRef cellref As Range, offsetBy As Long)

Application.Volatile

Dim accumulator As Double
Dim testWks As Worksheet
Dim Rng As Range
Dim tempRng As Range
Dim InRange As Range
Dim FoundCell As Range
Dim res As Variant
Dim ColToLookThrough As Range

'get the active sheet names.
With Application.Caller.Parent
Set InRange = Intersect(.UsedRange, .Columns("AX:AX"))
End With

'sum all sheet values
For Each Rng In InRange.Cells
If Not (IsEmpty(Rng)) Then
Set testWks = Nothing
On Error Resume Next
Set testWks = Worksheets(Rng.Value)
On Error GoTo 0
If testWks Is Nothing Then
'not a good worksheet name
Else
With testWks
Set ColToLookThrough = .Range("a:a")
res = Application.Match(cellref.Value, ColToLookThrough, 0)
If IsError(res) Then
'not found
Else
Set FoundCell = ColToLookThrough(res)
With FoundCell.Offset(0, offsetBy)
If IsNumeric(.Value) Then
accumulator = accumulator + .Value
End If
End With
End If
End With
End If
End If
Next Rng
SumTotal = accumulator
End Function

 

[Alexandre Brisebois (www.pointnetsolutions.com)]

Thabks Dave Im looking through it as we speak,
your help is greatly apreciated.

Regards,
Alexandre