calculation working overtime (excel 2002)

Topic

New Reply

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

Replies

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

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

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

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

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

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

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

Reply | Quote

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

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

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