calculation mode

[yaniv.dg]

hi all,
i build some macro in excel based on access,
at the start i'm doing manual calculate in the code so it will not
desturn the proccessing
but in the end of my automation i'm bringing back the
automatecalculation but then the proccessing is taking alot of time
even more then the accually vba proccessing,
how can i faster this issue?

 

[Dave Peterson]

I don't know a way of getting excel to speed up calculations--except for getting
a faster pc.

And here are a couple of sites that discuss slow performance. The first is by
Charles Williams and the second by David McRitchie:

http://www.decisionmodels.com
http://www.mvps.org/dmcritchie/excel/slowresp.htm

 

[Guest]

by going back to autocalculation excel recalculates all cells that it has
flagged for recalculation. so one of two ways to avoid this. Don't turn
calculation back to automatic... not really recommended or just copy
everything you created and paste it over itself as values only (ie
cells.pastespecial xlpastevalues) then turn the calculation back to auto.
This way there are no formulas which may need updating

cheers,
Scott

 

[yaniv.dg]

the question is if i'm doing copy pastespecial before doing
re-calculation,it might give falls values,am i correct?

 

[yaniv.dg]

by making the copy pastspecial before doing re-calcualtion,i might have
a false values,am i correct?

 

[Guest]

You'll only have false values if you later update a section that was a
dependant variable for another. For example, if you have column A as some
random data and in column B you multiplied it by 4, then when you
pastespecial you'll only get whatever values were there before you copied it
(not to mention you lose the formulai as well). So in response to your
question, yeah. By doing this before doing a recalculation you will not have
the same values, because there will be nothing to calculate. It will only be
values, no formulai.

What I suggest is that you run you're program in manual all the way. At the
end don't turn it back to automatic. If you wrote it in such a way that you,
or the macro, did all the calculation then you won't need the recalculate and
you can get rid of the recalculation. However, if you find that a lot of
your cells are wrong under manual calculation and the auto calculation
corrects this then you will either have to stick with the longer recal times
or fix up your code a bit so you don't need to recalculate.

(just in case you don't know what I mean, if you have say ten numbers in
column A and in column B you have B1=A1+5 etc, if you copy that and paste it
down till the end of column A then you will need to recalculate. if you set
up a loop like
dim i as integer
for i = 1 to 10
Cells(i, 2) = Cells(i, 1) + 5
next i
then the macro does all the calculation and you don't need to recalculate)

cheers,

Scott

 

[yaniv.dg]

hi scott,
your suggestion seems to be resounble for me i just dont know how to
implemet it.
my problematic array formula is based on index and math
for ex.
C B A
123 a-d !
123 e-g #
456 a-d &
456 e-g ~

so for example i need to bring the data from third culomn
so i have 123 from column A, c from column B
by the index match it will bring the "!" as a value from column C

for now i found a solution with index match by true value from the 3
statment it will bring the third column.these are the statment:
equal number from column A
c is is equal or bigger then left value of the range
c is equal or smaller then right value of the range

but this formula is making heavy re-calculation
do you have maybe a formula that will be much lighter then my current
for the calculation stage?

Scott כתב:

 

[Guest]

Anychance you can post that code? I'll see if I can find anything to help.

Just out of curiosity, how big is the data that you're using? you could
always use a Vlookup (or Hlookup table) and code that in. If it's taking
long for an array like this, maybe just try it with a Vlookup. Even if the
main code takes a bit longer you should be able to save on recalculation
time. See if this might help, if not post your code and I'll see what I can
do.

Cheers,
Scott

 

[yaniv.dg]

hi scott,
i'm working with a data above 12900 rows(alot of calculation time-the
time ok for vlookup).
i tried to think about a vlookup idea but didnt find a solution for 2
columns comparison.
this is my formula for now:
"=INDEX(filename & "'!R2C6:R10000C6,MATCH(1,(VALUE(RC[-10])=filename &
"'!R2C1:R10000C1)*(RC[1]>=LEFT(filename &
"'!R2C4:R10000C4,len(RC[1])))*(RC[1]<=RIGHT( filename &
"'!R2C4:R10000C4,len(RC[1]))),0))"

 

[yaniv.dg]

hi all,
i does anyone have an idea how to make a faster calculation for a
comperacy between 2 columns in order to bring a third colulm?
index/match is very problematic,i know that vlookup will probbly be
more safiecent,does anyone have an idea?
(e-mail address removed) כתב:

hi scott,
i'm working with a data above 12900 rows(alot of calculation time-the
time ok for vlookup).
i tried to think about a vlookup idea but didnt find a solution for 2
columns comparison.
this is my formula for now:
"=INDEX(filename & "'!R2C6:R10000C6,MATCH(1,(VALUE(RC[-10])=filename &
"'!R2C1:R10000C1)*(RC[1]>=LEFT(filename &
"'!R2C4:R10000C4,len(RC[1])))*(RC[1]<=RIGHT( filename &
"'!R2C4:R10000C4,len(RC[1]))),0))"

Anychance you can post that code? I'll see if I can find anything to help.

Just out of curiosity, how big is the data that you're using? you could
always use a Vlookup (or Hlookup table) and code that in. If it's taking
long for an array like this, maybe just try it with a Vlookup. Even ifthe
main code takes a bit longer you should be able to save on recalculation
time. See if this might help, if not post your code and I'll see what I can
do.

Cheers,
Scott