Help with a macro. Let's eliminate a loop...

[Bruce Bowler]

I know I'm being dense, but I can't figure out the "right way" to do the
following...

I have 2 workbooks, call them "WBA" and WBB".

I'd like to write a macro (I have several 100 pairs of workbooks to do)
that sets cell {row x, Col A} of WBB to 1 if the number in cell {x,I}
exists anywhere in column A of WBA, otherwise cell {x,A} in WBB should
be 0

My thought (in pseudo code) is to do something like

n = number of rows in WBB
m = number of rows in WBA
for i = 1 to N
WBB.cell(i,A) = 0
for j = 2 to m '2 because WBA has a header row
if WBA.cell.value(j,A) == WBB.cell.value(i,I) then
WBB.cell.value(i,A) = 1
exit for
endif
next j
next i

while I'm sure it would work, it's not terribly efficient (in fact it's
pretty darn inefficient), especially given that each of WBA and WBB may
contain somewhere between 15 and 20 thousand rows.

Is there a better way?

Thanks!
Bruce

--
+-------------------+---------------------------------------------------+
Bruce Bowler | A good deed never goes unpunished. - Gore Vidal
1.207.633.9600 |
(e-mail address removed) |
+-------------------+---------------------------------------------------+

 

[Bob Phillips]

Bruce,

Look at the Find method in VBA help, it should be quicker than what you are
doing.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[steve]

Bruce,

First clear WBB column A
Workbooks(WBB).Range(Rows(2),Rows(n)).ClearContents

Now fill in a formula
Workbooks(WBB).Range(Rows(2),Rows(m)).FormulaR1C1 = _
"=IF(RC9=[WBA]Sheet1!RC9,1,0)"

Now record a macro to copy and pastespecial xlvalues.

see if this works faster...

 

[Bruce Bowler]

Bruce,

Look at the Find method in VBA help, it should be quicker than what you are
doing.

Bob,

Thanks. Works well and with a little tweaking, I can probably speed my
searches up a bit more.

Bruce

--
+-------------------+---------------------------------------------------+
Bruce Bowler | A fool's tongue is long enough to cut his own
1.207.633.9600 | throat. - Thomas Fuller
(e-mail address removed) |
+-------------------+---------------------------------------------------+