Can anyone optimize the code? (to make it work faster)

Hi,

My first suggestion would be to create a new "helper' column (eg DI?) which you enter the vlookup section into & then refer to this column in the isna formula ie:
DI6 = VLOOKUP($A6,OFFSET([PVR.xls]Prices!$A$1,0,MATCH(DH$1,[PVR.xls]Prices!$A$1:$IA$1,0)-1,544,2),2,0))

and then DJ6 = if(isna(DI6), INDEX(DH:DH,ROW()-1), DI6)
This has effectively halved the amount of calculations done by the file.

Another suggestion is to ensure that any lookup ranges are limited to just the used range eg change "DH:DH" to the actual cells used.


Other ways of improving performance are suggested at the various pages of Charles Williams' which are linked on the below link:

HTML Code: 

hth,
Rob