speeding up the code

[mfegyver]

Dear All,

please , do anyone now if exists a way to improve the speed of the running time? I?m working in a NP-Hard problem, wich takes my about 14hours to run for n=500 instances (never mind for 1000...)

I?m using some formulas and this is taking to much time to recalculate this formulas during the running time.

I?m thinking 2 ways:

1- do not use any formula (calculate everything within the code, and what could be done by any other language)

2- maybe some of the wizards from this forum could teach me better technics.

many thanks and brgds
marcelo:help

[figment]

unfortunely we can not teach you any new technics or speed your code up, if we dont get a copy or example of what you already have.

[mfegyver]

well, my code is terribly dirty (i?m not a coder) but it works with numeric results, but one of the aims is to calculate it fast.

many thanks for helps
marcelo

[malik641]

We have an article to help you make your code faster:
Optimize your VBA Code (http://vbaexpress.com/forum/showthread.php?t=9882)

And as figment stated, seeing what you already have will save tons of time.

[mfegyver]

now with the attached file !

brgds
marcelo

[Norie]

The first thing you should do is lose all the Select/Selection pieces of code.

They just aren't needed.

For example this:

Range("J15").Select
ActiveCell.FormulaR1C1 = _
"=IF((RC[-3]) =1,(RC[-1]+R15C21),VLOOKUP(RC[-3]-1,R14C7:R1014C10,4,0)+RC[-1])" ' Col J = formula
Range("J15:s15").Select ' copia e
Selection.Copy ' cola
Range(Cells(15, 10), Cells(14 + n, 10)).Select ' pq??? col 10 - procv ' at? n linhas
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Can probably be replaced with this.

Range("J15").FormulaR1C1 = _
"=IF((RC[-3]) =1,(RC[-1]+R15C21),VLOOKUP(RC[-3]-1,R14C7:R1014C10,4,0)+RC[-1])" ' Col J = formula
Range("J15:s15").Copy
Range(Cells(15, 10), Cells(14 + n, 10)).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False