Ricky,

It took a bit of puzzling :)

Following would work for NUMERIC lookups. and IS LOTS AND LOTS faster
so you wouldn't need make it into values.

I Think you owe me one!


Sub MasterFill()

With Worksheets("Master")
.Names.Add Name:="cUnit", _
RefersToR1C1:="=MATCH(RC1,CurrentUnitList,0)"
.Names.Add Name:="cDate", _
RefersToR1C1:="=MATCH(R1c,CurrentDateHeader,0)"
.Names.Add Name:="pUnit", _
RefersToR1C1:="=MATCH(rC1,PreviousUnitList,0)"
.Names.Add Name:="pDate", _
RefersToR1C1:="=MATCH(R1c,PreviousDateHeader,0)"
.Names.Add Name:="cMatch", _
RefersToR1C1:="OFFSET(CurrentBase,cUnit,cDate)"
.Names.Add Name:="pMatch", _
RefersToR1C1:="=OFFSET(PreviousBase,pUnit,pDate)"
.Names.Add Name:="pMatchNoErr", _
RefersToR1C1:="=IF(ISNA(pMatch),0,pMatch)"

With .[d4:in150]
Application.Calculation = xlCalculationManual
.ClearContents
.Cells(1).Formula = _
"=IF(ISBLANK($A4),"""", LOOKUP(cMatch-pMatchNoErr," & _
"{-1E+32,0,1E-32},{""x"","""",""y""}))"
Application.StatusBar = "filling master..."
.FillRight
.FillDown
.Calculate
Application.StatusBar = "writing values..."
'.Formula = .Value
Application.Calculation = xlCalculationAutomatic
Application.StatusBar = False
End With
End With


End Sub






keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Ricky Pang wrote:

[I had to repost as my original message from 3 days ago crashed]
Hello Experts,
This is an effort to speed things up. I am using 248 out of a possible
256 columns, and 150 rows down...and it's slow. Each cell in my
"Master" tab has this long formula, starting from D4 down and across.
It compares the "Current" tab with the "Previous" tab and produces a
result. Is there a way to convert this formula into code? and have
it in every cell, from D4 to IN150, so that it'll run faster?


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!