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

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

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)" wrote:
>
> 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

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

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)" wrote:
>
> 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

do you have an example of the appluication match ?

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

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)" wrote:
>
> 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

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

Regards,
Alexandre