Time of execution

[Marius44]

Good night
I have an Excel file with 2 Sheets.
In the first (named CLAIM) it there is a list of codes in the coloumn A and other codes in the P-Q-R-S-T columns
In the other (named DELIVERY) it there is a list of codes in the coloumn A and the state of the order in coloumn CK
I must collate all the codes of the with the. P-Q-R-S-T of the CLAIM Sheet with the codes in coloumn A of the DELIVERY Sheet. If the codes are identical I control the string in the same row in coloumn CK; if the string is "Consegnato" the color of the cell in CLAIM Sheet must be green.


I have made two macros
1°) I have allotted the codes to of the variable and with For … Next cycles I compare codes
2°) I have used AutoFilter to compare codes


The two macro functions perfectly.
Problem
I launch first the macro n.1- time of execution= xTime
Then I launch the macro n.2- time of execution= yTime (very near to xTime)


Then I launch again the macro n.1- time of execution= xTime multiplied 7
Then I launch again the macro n.2- time of execution= yTime (around)


What caused this strange behavior?


Here is my file
https://www.dropbox.com/s/v2ob6qr7uqgxjsa/test_prove.xlsm?dl=0

Thank you in advance and there I pray of excuse my English


I wait for you reply.
bye,
Mario

in Italian
Ho una cartella Excel composta da 2 Fogli.
Nel primo (CLAIM) vi è un elenco di codici nella col.A ed altri codici nelle colonne P-Q-R-S-T
Nel secondo (DELIVERY) vi è un elenco di codici nella col.A e lo stato dell’ordine in col.CK
Devo confrontare tutti i codici delle col. P-Q-R-S-T del Foglio CLAIM con i codici in col.A del Foglio DELIVERY. Se i codici sono identici controllo la stringa presente nella stessa riga in col.CK; se la stringa è “Consegnato” il colore della cella in Foglio CLAIM deve essere verde.

Ho impostato due macro diverse
1°) ho assegnato i codici a delle variabili e con cicli For … Next effettuo i confronti
2°) ho utilizzato AutoFilter

Le due macro funzionano perfettamente.
Problema
Eseguo per prima la macro n.1 – tempo di esecuzione = xTime
Poi eseguo la macro n.2 – tempo di esecuzione = yTime (molto vicino a xTime)

Eseguo di nuovo la macro n.1 – tempo di esecuzione = xTime x 7
Eseguo di nuovo la macro n.2 – tempo di esecuzione = yTime (circa)

Perché questo strano comportamento?
Allego il file

 

[Rijnsent]

B'giorno Mario,
I gave your file a try, but for me the processing times remain the same... So I have no clue what causes that, is it the same on a different computer?
Cheers,
Koen

 

[Marius44]

Hi Koen


Thank you for you kindly replay
Yes, it is the same on a different PC (a friend of mine has try it with the same results).
It is very strange, for me, that you obtain the same times. Do you have followed the mode from me indicated?


I thank you in advance.
Cheers,
Mario

 

[Rijnsent]

Hi Mario,

What I did:
-downloaded your file and opened it
-press button "Number 1" -> tempo = 2,44
-press button "Number 2" -> tempo = 0,92
-press button "Number 1" -> tempo = 2,39
-press button "Number 2" -> tempo = 0,93
If I keep pressing button 1 and than number 2, the times for number 1 are somewhere from 2,3-2,5 and for number 2 somewhere 0,9-1,1

What times do you get with this file?

Cheers,

Koen

 

[Marius44]

Hi Koen
thank you for your reply

This are my times


-press button "Number 1" -> tempo = 0,984375
-press button "Number 2" -> tempo = 1,4765625
-press button "Number 1" -> tempo = 7,08203125
-press button "Number 2" -> tempo = 1,62109375

Bye,
Mario

 

[Rijnsent]

Stranissimo... What you could try (no clue what will work, VBA can be a mystery):
-in Sub SfondoColore also add the Application.Calculation = xlCalculationManual -> if you start 1 with settings to Manual, step 2 will change that to automatic...
-try a Debug.Print "ONE", uriga, uriga1 -> my first thought is that any of those two variables get set too high somehow (not at my pc)
-in your code at severa locations add code like: debug.print "Step 2: ", ftime - itime to find out where exactly the delay is caused
-in SfondoColore, clean up at the end, you have some arrays still around: DP, RP, SH and CK

Ciao,

Koen

 

[Marius44]

Hi Koen

I have followed your hint and I have noted that the problem is the itemizes of the type of value when Excel allot to the variables.

If I in all the cycles omit " .Text " the macro-time is a chip.


Thank you very much for the help and to good render.

Ciao,
Mario