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

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 |
|
+-------------------+---------------------------------------------------+

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

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)

"Bruce Bowler" wrote in message
...
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 |
|
+-------------------+---------------------------------------------------+

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

Bruce,

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

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...
--
sb
"Bruce Bowler" wrote in message
...
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 |
|
+-------------------+---------------------------------------------------+

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

On Wed, 29 Oct 2003 19:26:32 +0000, Bob Phillips put fingers to keyboard
and said:

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
|
+-------------------+---------------------------------------------------+