LinkBack URL
About LinkBacks
Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by Fotis1991; 10-07-2013 at 08:08 AM.
please don't use range names, I can not understand what you are doing.
If solved remember to mark Thread as solved
Please see code without range names below.
Please Login or Register to view this content.
Last edited by Fotis1991; 10-07-2013 at 08:08 AM.
Hi
Just looking at your code line by line.
Please Login or Register to view this content.
Last edited by mehmetcik; 10-06-2013 at 12:15 PM.
Hi, John Vieren,
like stated it´s the mere number of loops that can hardly be manipulated or optimized. Since yoiu turned off calculation you need to recalculate on each loop, and as it is set from your thread title you want to add the numbers to thes existing numbers in the other range.
I set a sample sheet holding teh loop number in A1, simple formulas like
for some cells in the range to calculate (but I kept my Excel on aitomatuce calculation). What I moved was the copying of formats - I think it´s good enough to do it once after the round of loops is done. Fiurthermore I split the macro up in slices (here of 8 rounds a 2500 loops). Each turn on my PC will take between 50 and 60 seconds, the toal püackage runs for about 8 minutes (not really something that I like: my Excel being blocked that long).Formula:
Please Login or Register to view this content.
Code I used:
Ciao,Please Login or Register to view this content.
Holger
Use Code-Tags for showing your code: [code] Your Code here [/code]
Please mark your question Solved if there has been offered a solution that works fine for you
Thank you for your response, please see further commentary below.
MEHMETCIK Why bother with this line? In line 7 you are overwriting this whole selection
JOHN VIEREN Because each time i run the macro, i need to clear the data from the prior macro run.
MEHMETCIK Why is this line within your loop? it will be actioned 20, 000 times.
JOHN VIEREN I am reading in 20,000 data records and each record is used to make certain calculations and the output for each record is sumed (i.e calculations for 20,000 are added togeter)
MEHMETCIK This sub is run 20, 000 times so is it efficient ?
JOHN VIEREN No not eficient it runs slow hence my question.
MEHMETCIK The next three lines can be replaced by a single line. The formating should be done outside the loop if required.
JOHN VIEREN Yes I agree this can be improved, thank you.
MEHMETCIK 'Replace your three lines
JOHN VIEREN This will not add up the results for the 20,000 records will it?
Last edited by John Vieren; 10-10-2013 at 05:53 AM.
Dear Holger,
Thanks for your response.
I am not an expert with VBA and dont fully understand your code.
In any case, why would splitting it up into 8 rounds make it run any faster?
And yes I agree the formating can be done at the end and this has made the macro run faster.
Kindest regards JV
Hi, John Vieren,
it doesn´t run faster but you feel like that as you do have a smaller amount of time to wait until Excel is free again for other work.
Ciao,
Holger
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks