• calculation working overtime (excel 2002)

    Home » Forums » AskWoody support » Productivity software by function » Visual Basic for Applications » calculation working overtime (excel 2002)

    Author
    Topic
    WSalexanderd
    AskWoody Lounger
    July 17, 2003 at 7:10 pm #390623

    i have a workbook with 3 sheets which hold the results of different calculations, each sheet is calculated before going to the next sheet (this works fine as individual parts of the macro),.
    on the macro finishing only the last sheet shows the calculation has been updated( the sub totals are shown as calculating at the base of the screen) and the sheet show that it is ready for printing.
    how can i stop the other two sheets from being over written with the end calculation

    Reply | Quote
    Viewing 0 reply threads
    Author
    Replies
    • WSJohnBF
      AskWoody Lounger
      July 17, 2003 at 8:26 pm #694550

      Hi. Your explanation doesn’t fit the problem. Can you explain why the first sheets are overwritten? Does the macro act on multiple sheets at the same time? Are you saying that after the macro has run, the “calculate” notification shows in the status bar? If so, you may want one of your last commands in the macro to be:

      Application.CalculateFull

      It would help if you posted the code and noted which version of Excel is being used.

      Reply | Quote
      • WSalexanderd
        AskWoody Lounger
        July 20, 2003 at 2:31 pm #695136

        further to my request for help .
        i have 3 sheets
        1) barry 94
        2) jim 22
        3) ads1
        the three sheets have identical programming except for the line
        ActiveCell.FormulaR1C1 = _
        “=IF(AND(RIGHT(RC[-8],5)=””TOTAL””,RC[-2]>650,RC[-2]650,RC[-2]<750),""YES"","""")"

        Range("I4").Select
        Selection.copy
        Range("I5:I1000").Select
        ActiveSheet.Paste
        Range("I4").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Application.MaxChange = 0.001
        ActiveWorkbook.PrecisionAsDisplayed = False
        Calculate
        Range("A3").Select
        Selection.autofilter
        Selection.autofilter Field:=9, Criteria1:="YES"
        _'########################################################################
        If i alter the programme as follows i get a better result which leads me to think i have a timeing problem. cn you help with a solution.
        '#########################################################################
        Range("A3").Select
        Selection.autofilter
        Selection.autofilter Field:=9, Criteria1:="YES"
        Sheets("jim 22").Select
        Range("A3").Select
        Selection.autofilter
        Selection.autofilter Field:=9, Criteria1:="YES"
        Sheets("barry 94").Select
        Range("A3").Select
        Selection.autofilter
        Selection.autofilter Field:=9, Criteria1:="YES"
        alex

        Reply | Quote
        • WSHansV
          AskWoody Lounger
          July 20, 2003 at 9:19 pm #695177

          I don’t have the slightest idea what your macro is doing, but it can be written much more efficiently by using ranges instead of selecting cells or ranges repeatedly. Also, there are several instructions that seem to be superfluous. Here is a macro that does the same as your code, minus the instructions that I think should be omitted:

          Sub Test()
          Dim wsh As Worksheet
          Set wsh = Worksheets(“barry 94”)
          wsh.Columns(“H:M”).Delete
          With wsh.Range(“A4”)
          .Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(7)
          .Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(7)
          End With
          wsh.Range(“I4”).FormulaR1C1 = _
          “=IF(AND(RIGHT(RC[-8],5)=””TOTAL””,RC[-2]>650,RC[-2]<750),""YES"","""")"
          wsh.Range("I4").Copy Destination:=wsh.Range("I5:I1000")
          Application.CutCopyMode = False
          wsh.Range("A3").AutoFilter Field:=9, Criteria1:="YES"
          End Sub

          If you would like more help, please attach a demo workbook, so that we have an idea of what your data look like. Replace confidential info by dummy data, if necessary.

          Reply | Quote
          • WSJohnBF
            AskWoody Lounger
            July 21, 2003 at 1:45 am #695216

            Alex, and Hans, I can shed a little light on the problem, but I’m also running into a problem with the code, and I hope Hans or another Excel VBA guru can help me out. I attached the demo worksheet I built to answer the problem. The code does a little more than Hans code, as it loops through the three Worksheets. I won’t repeat the code in the post, since it’s in the attachment.

            As Hans notes, it’s difficult to know what you are doing without appropriate sample data, but there seem to be some duplication and contradiction in the code, for example

            .Range(“A4”).Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(7)
            .Range(“A4”).Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(7)

            the second line overwrites the first, and messes up the formulas later posted in Column I, so I used only the first line, grouping by the Column A totals.

            It appears that Subtotal Outlines and AutoFilter do not work well together. When you use an Autofilter in Excel, it excludes rows that do not meet the criteria from any calculations. So when you apply the Autofilter to the outline, all the data rows are excluded, so the Subtotal rows then calculate to zero, and then Autofilter doesn’t find any “YES” values. I used two fixes to covercome this problem in the code: first, I remove the outline, which leaves the Subtotals; second, I turn the column “I” formulas into values.

            That done, I still have a problem. If I run the code without the Autofilter line, and then Autofilter manually, I get the desired result. But if I run the Autofilter line by code, I get anomalous results (see code comment). What’s going on?

            Alex, as Hans notes, your code was all written through the use of the macro recorder and so is pretty inefficient, particularly regarding the unnecessary use of Select. My code fixes some of those problems, but could be improved further. If you are planning to get into Excel VBA, I recommend you get a book and work through it.

            Reply | Quote
            • WSHansV
              AskWoody Lounger
              July 21, 2003 at 7:12 am #695253

              Hi John,

              I think we need to see some sample data and to get an idea of what Alexander wants to accomplish. I don’t know why one would want to combine Subtotals and AutoFilter.

              Reply | Quote
            • WSalexanderd
              AskWoody Lounger
              July 22, 2003 at 6:44 pm #695766

              sorry for the delay but ntlworld server has been down and not only that i have brought the excel file but not the latest *.bas file home with me, i will try again tommorow.

              Reply | Quote
            • WSJohnBF
              AskWoody Lounger
              July 23, 2003 at 3:48 am #695895

              The VBA code should be in the Workbook, you don’t need the .BAS file.

              Reply | Quote
            • WSalexanderd
              AskWoody Lounger
              July 23, 2003 at 5:56 pm #696179

              sorry for the delay in replying but!!
              I thank you all for your comments.

              it is some times hard to tell one’s peers what one has done without causing them to shake in their boots, but nothing lost, I will endeavour to give a further insite into the reason for and the subsequent programme.

              I work in a sales office in which we also have control of the dispatch of all orders taken, the system is so flexible that as shelves are re-stocked with new goods the database is updated, and orders which have reached a certain value are released for packing and delivery.

              The system is volatile in as much that the file can have 2400 lines as the attached file or some 7000 line as I have seen in the past 2 days.

              I will list the way that I have tried to programme the macro. The file is transferred from a UNIX computer as a CSV file,
              Which make it harder to work with?

              The manual working of removing all superfluous parts could take 3 hours with interruptions the macro takes approx 1.5 minutes.

              1) Remove all accounts on stop——————————————– late payer

              Reply | Quote
            • WSalexanderd
              AskWoody Lounger
              July 22, 2003 at 6:52 pm #695768

              hi john i agree with your comments about a book , infact i bought “excel 2000 vba published by wrox” what a wast of money for me , examples not working and the new staff cannot get them to work. can you suggest a good book to buy with lots of worked examples (that do work)

              alexanderd

              Reply | Quote
            • WSJohnBF
              AskWoody Lounger
              July 23, 2003 at 3:49 am #695894

              See post 268734 for a discussion on Excel VBA books. The wrox books are usually good, but if you have their reference book, it may assume too much for a beginner.

              Reply | Quote
    Viewing 0 reply threads
    Reply To: calculation working overtime (excel 2002)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    May 2025
    S M T W T F S
     123
    45678910
    11121314151617
    18192021222324
    25262728293031

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.