Compare tabs using Double Lookup, in VB code

  • Thread starter Thread starter Ricky Pang
  • Start date Start date
R

Ricky Pang

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 D5 down and across.
It compares the "Current" tab with the "Previous" tab and produces a
result. Is there a way to convert this formula to code so that it'll
run faster?

This formula is from cell [D5]:
=IF(ISBLANK(A5),"",
IF(OFFSET(CurrentBase,MATCH($A5,CurrentUnitList,0),MATCH(D$1,CurrentDate
Header,0))>OFFSET(PreviousBase,MATCH($A5,PreviousUnitList,0),MATCH(D$1,P
reviousDateHeader,0)),"Y",
IF(OFFSET(CurrentBase,MATCH($A5,CurrentUnitList,0),MATCH(D$1,CurrentDate
Header,0))<OFFSET(PreviousBase,MATCH($A5,PreviousUnitList,0),MATCH(D$1,P
reviousDateHeader,0)),"X","")))

Formula Interpretation:
CurrentBase is cell A1 of the "Current" tab
CurrentUnitList is cell A2:A150 of the "Current" tab
CurrentDateHeader is cell B1:IN1 of the "Current" tab
PreviousBase is cell A1 of the "Previous" tab
PreviousUnitList is cell A2:A150 of the "Previous" tab
PreviousDateHeader is cell B1:IN1 of the "Previous" tab

In the "Master" tab,
If [A5] is blank, then leave the cell empty.
If not;
Perform a double lookup by row [A5] and by column [D1] as the search
criteria.
Go to "Current" tab and get the intersect.
Go to "Previous" tab and get the intersect.
If the "Current" intersect is > "Previous" intersect then the result is
"Y".
If the "Current" intersect is < "Previous" intersect then the result is
"X".
If the "Current" intersect is = "Previous" intersect then the result is
"" [blank cell].

Thank-you in advance you, the geniuses, that come to my rescue.

Ricky
 
Ricky

there's lots of different ways. but i'm not sure
the using code is going to speed it up.

In general VBA functions are lot slower that worksheetformulas.
and a more efficient formula might well do the trick better.
it depends on a lot of factors.

First i suggest that you add a extra column and an extra row.
Get the indexes THERE, then point the rest of the formulas to those
indices and extract from PrevSheet with a simple
=INDEX(prevsheet!BigRANGE,b5,e2)

if you want then zip and email to address below.
so i can have a look myself.


keepITcool

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


Ricky Pang said:
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 D5 down and across.
It compares the "Current" tab with the "Previous" tab and produces a
result. Is there a way to convert this formula to code so that it'll
run faster?

This formula is from cell [D5]:
=IF(ISBLANK(A5),"",
IF(OFFSET(CurrentBase,MATCH($A5,CurrentUnitList,0),MATCH(D $1,CurrentDate
Header,0))>OFFSET(PreviousBase,MATCH($A5,PreviousUnitList,0),MATCH(D $1,P
reviousDateHeader,0)),"Y",
IF(OFFSET(CurrentBase,MATCH($A5,CurrentUnitList,0),MATCH(D $1,CurrentDate
Header,0))<OFFSET(PreviousBase,MATCH($A5,PreviousUnitList,0),MATCH(D
Click to expand...
$1,P
reviousDateHeader,0)),"X","")))

Formula Interpretation:
CurrentBase is cell A1 of the "Current" tab
CurrentUnitList is cell A2:A150 of the "Current" tab
CurrentDateHeader is cell B1:IN1 of the "Current" tab
PreviousBase is cell A1 of the "Previous" tab
PreviousUnitList is cell A2:A150 of the "Previous" tab
PreviousDateHeader is cell B1:IN1 of the "Previous" tab

In the "Master" tab,
If [A5] is blank, then leave the cell empty.
If not;
Perform a double lookup by row [A5] and by column [D1] as the search
criteria.
Go to "Current" tab and get the intersect.
Go to "Previous" tab and get the intersect.
If the "Current" intersect is > "Previous" intersect then the result is
"Y".
If the "Current" intersect is < "Previous" intersect then the result is
"X".
If the "Current" intersect is = "Previous" intersect then the result is
"" [blank cell].

Thank-you in advance you, the geniuses, that come to my rescue.

Ricky
Click to expand...
 
Back
Top