Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 | | +-------------------+---------------------------------------------------+ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 | | +-------------------+---------------------------------------------------+ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 | | +-------------------+---------------------------------------------------+ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 | +-------------------+---------------------------------------------------+ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Eliminate macro warning when you open a workbook | Excel Discussion (Misc queries) | |||
Create a macro to eliminate certain words from several columns | Excel Worksheet Functions | |||
Macro to eliminate extra space | Excel Discussion (Misc queries) | |||
macro to eliminate repeating account numbers | New Users to Excel | |||
macro to eliminate spaces between words | Excel Discussion (Misc queries) |