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

0
7/20/2005 1:16:29 PM
excel 39879 articles. 2 followers. Follow

6 Replies
508 Views

Similar Articles

[PageSpeed] 17

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
0
petersod (12005)
7/20/2005 1:43:57 PM
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

0
7/20/2005 2:06:44 PM
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
0
petersod (12005)
7/20/2005 2:15:43 PM
do you have an example of the appluication match ?

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

0
7/20/2005 2:49:08 PM
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
0
petersod (12005)
7/20/2005 4:37:22 PM
Thabks Dave Im looking through it as we speak,
your help is greatly apreciated.

Regards,
Alexandre

0
7/20/2005 5:26:56 PM
Reply:

Similar Artilces:

Killer macro idea for business mailings
One of you programmers should write a macro to validate a city from zip code. I receive excel files from response cards and a lot of people will put some random letters for their city instead of the actual city name, or maybe they'll put the wrong abrieviation for their state. It would be awesome if all the zipcodes could validate the state field and change if necessary and the same for the city fields too. Okay, have at it :) Okay, well good idea I think anyways, but it turns out that my columns just weren't wide enough so it didn't display more then 2 letters. ...

How do I create a sample in Excel?
I am trying to create a sample, needing every 20th record out of around 3000 records. Mary, You need a VBA procedure. Sub AAA() Dim RowNdx As Long Dim DestRng As Range Set DestRng = Worksheets("Sheet2").Range("A1") For RowNdx = 1 To 3000 Step 20 Rows(RowNdx).Copy Destination:=DestRng Set DestRng = DestRng(2, 1) Next RowNdx End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mary" <Mary@discussions.microsoft.com> wrote in message news:ADE0AD7F-B459-41AE-8B2...

*major* functional issue with MOSS/WSS?
We are looking to move a big site over to using MOSS in a big as an alternative to shared network drives (ugh). One of the biggest issues is sending docs as attachments via email for others to edit. We'd like to get everyone to use the 'send a link' functionality from thew Sharepoint DL BUT when the user receives the email and clinks on the embedded link the doc opens read only. They then cant edit and save their changes. Seems a big an counter intuitive functional flaw. Are we missing something here? Is there any easy (for the end user) way to send those links to a doc ...

Excel 2007 Run Remote/Server/Network/Shared Macro From Custom Butt
In Excel 2003 this was straightforward: add the add-in regarless of location and assign it to a button. I have tried several things in 2007 without success. I've followed the usual links (including to Rondebruin and the others!), local add-ins running remote add-ins, abused my keyboard, kicked some furniture, etc... Still I haven't found a solution. I'm sure the solution isn't as easy as in 2003 but if anyone knows it I would appreciate some advice/directions. Thanks, SQLServant ...

Audio does not work on other end of video call
I have 3 or 4 people that I have tried to setup video calls thru messenger and only one of them can hear me clearly. One can hear me but I have to yell. Another cannot hardly hear me at all and is choppy. The video portion works fantastic on both ends, as well I can hear everyone crystal clear. I also know that the other party's systems seem to be working well because I can actually hear their pc's making noise when I type a message or when they play something. So I don't think they have speaker issues. Does anyone know what the problem could be? Everyone i...

office 2000 [excel] cannot open previously OK file. Help
When trying to open a Microsoft Excel Work Sheet that I had accessed modified and saved to only yesterday [Office 2000] a window pops up with: "name.xls cannot be accessed. The file may be resd-only, or you may be trying toaccess a read only location. Or, the server the document is stored on may not be responding." Older files stored on the same mediums ;HD, Flash memory or 3.5in. diskette are accessed with no problem. This is on a network drive? ...

Excel and some disappeared sheets
Anybody has the same or similar experiences : - I worked with excel file / closed as usually - new day opened and unfortunatelly some very important sheets disappeared / why ? Don=B4t know - I had about 7-9 sheets just now only 3 ones are there but not so important as another ones - the file has original size / I quickly looked into file by normal text editor only for reading and all relevant data are there, however if I open file by Excel, NO original sheets or data are there. - this file is VERY IMPORTANT for me - why happened ? Don=B4t know since I ask anybody for help Thanks in advan...

Formatting a subtotal line with a macro
I already have in use a macro that runs a sort and subtutal function. I would like to format the subtotal row using visual basic since there is already a macro in place. Hi Look at this: ActiveCell.Font.Bold = True Regards, Per "DJDKAL" <daniel.kalfayan@hotmail.com> skrev i meddelelsen news:A86040B0-B4C4-4E11-A80C-E80132C9710F@microsoft.com... >I already have in use a macro that runs a sort and subtutal function. I > would like to format the subtotal row using visual basic since there is > already a macro in place. This is what I've g...

Can't create Organizational Forms Library in Exchange 2003 with SP
Hello, I cannot create an Organizational Form in EFORMS REGISTRY folder (from First Administrative Group->Folders->Public Folders->EFORMS REGISTRY in ESM). When I right-click the EFORMS REGISTRY folder and select New, there is no Organization Form. Instead, I only see Public Folder in the popup menu. Do you have any idea why Organizational Form menu does not show? My Exchange Server is Exchange 2003 with SP2. The login user is Administrator. Could you please help me? Thank you very much. Yang Is that account member of "Enterprise Admins" group? Yang Zhang wrote: &...

backup file won't work!!
OK, now I've really had it! This is my last resort! Several years ago, while using Money 97, our computer crashed. We had backed up every time we used money, so I thought we were OK. However, we could never get the backup file to pull up after fixing the computer and everything was lost. I vowed that this would not happen again, so I tried backing up on a ZIP disk instead, and then on CDs. I also got on this newsgroup and got the advice that I needed to first copy the backup file onto the desktop and open it from there. I tried doing that with the old program, and it seeme...

How to make Run Integration work in GP 10
Can anyone tell me how to make the Run Integration tool work in GP 10. We just did our first client upgrade to GP 10 and the client has only 1 integration that they used to be able to run via the RUN INTEGRATION Tool. Any help would be greatly appreciated. -- Tammy Chavez I have the same probelm...did you get any answers to this? I have to go into Integration Manager to run it b/c Tools>Run Integration does nothing. I even checked the ini files to make sure the path is correct. "Tammy Chavez" wrote: > Can anyone tell me how to make the Run Integration tool work in...

Special Pasting a work book with many sheets and formulas
I have a workbook with many sheets that all have formulas and links to other data. I want to save the workbook as another name with all the worksheets keeping the values only (no links or formulas). Is there a quick way to do this for everysheet without having to special paste every sheet in the workbook. So can I save everysheets data values at workbook level. See this page for a code example http://www.rondebruin.nl/values.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "lex63" <lex63@discussions.microsoft.com> wrote in message news:ED708...

VB Macro #2
I have a macro that is generating Excel files. When the macro runs, it saves two different tabs in the workbooks as values. It needs to test and wait until the formulas that are pulling amounts from another database are correctly populated with the amounts. Right now, I am getting N/A# instead of amounts for some, but not all, of the files. I think the problem is that the formulas have not completed pulling the amounts before the macro pastes that tab as zeros. (N/A# is what appears when the formulas have not completed calculating - once they have completed, the N/A# is replaced ...

How do I create a click on + symbol to open a root and click on -.
I'm looking to create an excel file with drop down menus. I'd like to have a category. Click on the "+" symbol and the category opens up and shows all of the subcategories. Each category can further be opened if I so choose. Each category can be have a number total associated with it. When you click the "-" symbol. The subcategories close and the sum total of all subcategories is shown in the category total. example. creating a budget. Category is utilities sub categories are: phone, cable, electric, gas, etc... Monthly utility total ...

Problem with Update function of CRecordSet class
Hi All When I use from Update function of CRecordSet class, it apply to table after a few time(for example 1 second) when I need to see it at time. Thanks ...

Problem with macro assigned to custom toolbar button
Problem with macro assigned to custom toolbar button I am having a probelm with macros assigned to a custom button on custom button. I am using excel 2000. 1. I have created a macro called "Jump" in a workbook named "Test1" 2. I then created a custom toolbar and added a custom toolbar button t that toolbar. 3. Through the customize dialog box I assign the Macro "Jump" to th custom button. Everything works fine UNTIL. If I do a SAVE AS for the workbook "Test1" and call it something els like "Test2" and then close "Test2" an...

OL2002 Problem : HELP, SOS, MAYDAY
Hi, I have an IMAP account and OL2002 as client. I have checked for my account ( tools, send/receive settings, define send/receive groups, <my_account>, edit) download item description only. It works only the first time I start OL2002, but it does not work anymore for subsequent automatic/manual send/receive operations. Please could someone really help me ? Thank you in advance, Patrick ...

Data migration - Adventure Works
Hiya... I have a company where the adventure works db has been used and had a lot of data populated into the system. We have now purchased MSCRM and have obtained the company reg keys. What is the easiest way to get the data from the 1 system to the next? We will be establishing a new AD domain and users for the new system.... Data Migration Framework? Redeployment Framework? ;) redeploment tools http://www.microsoft.com/downloads/details.aspx?FamilyID=bfced393-61db-49af-9a50-4a90b311fa7d&DisplayLang=en -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "funboy...

how create Quota filter in WIndows 2003 R2 using Script
Hi all I need create quota filter in Server 2003 R2 using vbscript. quota filter should be applied to directories and not by users. I searched information about it on google without success. thank's in advance Daniel Hi Daniel. First you need open your FSRM (File Server Resource Manager), then you create a quota template, you must specify if your quota is "software" (just monitoring, but never deny the user) or "hardaware" (deny users when they use 100% of the quota), you must specify if you want send e-mail to user when this user use...

Creating a chart based on the data in an embedded worksheet
Hi, I have a worksheet with several embedded worksheets. I would like to create a chart based on the data of one of the embedded worksheets without putting the chart in the embedded worksheet. I have tried unsuccessfully to do this. I just wondered if anyone knew how to do it. Thanks, JK JK - You're embedding worksheets within worksheets? Why? Why not just insert the worksheets in line with the main worksheet? To open or edit the embedded worksheet, the parent Excel has to open another instance of Excel, and the chart on the outside of this other instance will never be able to acce...

creating a spredsheet and log the info into another spredsheet
hello, I have a excel sheet that is printed out and a cashier manual enters information, invoice number, invoice amount, cash amount, check amount, amex amount.... I would like to have the cashier input this information on her PC and print a copy to go along with the deposit and at the same time log the information into a google excel document I created. Any ideas how this can be done? ...

Help With Margin Formula
Hello, I need help with a margin forumla (calculated from retail). Say I have a cost of $10.00, and I need the formula to calculate a 40% margin from retail. So the retail should end up at $16.67. Not sure how to get from $10.00 to $16.66, I just know the cost and the margin I need to make. Thanks JR =A1/(100%-40%) -- Kind regards, Niek Otten "JR" <gaspower@aol.com> wrote in message news:eGszf.424$2O6.53@newssvr12.news.prodigy.com... > Hello, > I need help with a margin forumla (calculated from retail). Say I have a > cost of $10.00, and I need the formul...

GETPIVOTDATA data_field help
When I enter a reference to a cell, eg A2, as the value for the data_field item in the GETPIVOTDATA I get a #REF error. However, when I instead type the value of A2, eg 2003, manually into the GETPIVOTDATA formula, it works. Can anyone please help--I want this formula to vary based on what is in A2. Cheers This function is so variable in action, and difficult to handle, that have given it up in favour of VLOOKUP. In fact, having no problem i use with pivot tables -- Message posted from http://www.ExcelForum.com Try copying the headings from the pivot table, and pasting them into the ce...

Creating Exchange 2003 organization
Is it possible to have two Exchange Organizations in the same domain? I need to reproduce a problem and was hoping to install an Exchange server to an existing domain however I need the organization to be different. Is this possible? When I installed EX 2003 I wasn't prompted for information other than location of files. Thanks in advance On Mon, 28 Feb 2005 08:09:03 -0800, "RP" <RP@discussions.microsoft.com> wrote: >Is it possible to have two Exchange Organizations in the same domain? I need >to reproduce a problem and was hoping to install an Exchange server...

Exchange server crashed, please help....! Need to restore two priv.edb and pub.edb files into one....!
Hi Guys, I was wondering if I could get some help with the following problem we are having on our company. Here is the scenario; Our Windows NT 4.0 SP4a server running Exchange 5.5 SP4 crashed (Server 1) due to the exchange database reaching its 16 Gig's max limit. I went ahead and moved some mailboxes' e-mails to a few .pst files in order to make some space. This worked ok. Then, I decided to build another exchange server (Server 2) to moved some mailboxes and alleviate the load. Once the server was ready and configured as part of the current exchange site, I went ahead and move...