Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    calculation working overtime (excel 2002)

    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

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: calculation working overtime (excel 2002)

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculation working overtime (excel 2002)

    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]<750),""YES"","""")"
    where the values are changed.
    If i re- calculate just before each sheet is closed i lose the value "YES" as in the following
    '################################################# ################
    Range("A2").Select
    Sheets("barry 94").Select
    Sheets("barry 94").Name = "barry 94"
    Columns("H:M").Select
    Selection.delete Shift:=xlToLeft

    Range("A4").Select
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(7), _
    Replace:=False, PageBreaks:=False, SummaryBelowData:=True
    Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(7), _
    Replace:=False, PageBreaks:=False, SummaryBelowData:=True
    Range("I4").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(AND(RIGHT(RC[-8],5)=""TOTAL"",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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: calculation working overtime (excel 2002)

    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.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: calculation working overtime (excel 2002)

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: calculation working overtime (excel 2002)

    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.

  7. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculation working overtime (excel 2002)

    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.

  8. #8
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculation working overtime (excel 2002)

    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

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: calculation working overtime (excel 2002)

    The VBA code should be in the Workbook, you don't need the .BAS file.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: calculation working overtime (excel 2002)

    See <post#=268734>post 268734</post#> 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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculation working overtime (excel 2002)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •