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