I have about 10 cells in a row that contain strings. These strings can be null to paragraphs of text. I need to find out if a particular key word exists in any of the cells, for each row through hundreds of thousands of rows, then this gets nested in another loop - so I need this to be fast! My first step is to read all of the rows into an array. (Doing any operations direct in cells is a performance killer!) The question is- Should I concatenate all of the elements of the array for the row and run instr once to look for the string, or should I loop through each element of the row and perform the instr test on each one? I suspect that the latter method is faster.
Which of the two techniques for creating hitlist above is faster?
Code:
dim snap() as variantdim i as integer 'my rowdim k as integer 'my columndim HitList as new collectionset snap = range("A1:Z99999") 'for example - some populated rangefor i = 1 to ubound(snap) if instr(snap(i,1) & snap(i,2) & snap(i,3) & snap(i,4) & snap(i,5), "SearchWord") > 0 then HitList.add snap(i,1) snap(i,1) 'create a collection with no duplicates end ifnext'ORfor i = 1 to ubound(snap) for k = 1 to ubound(snap,2) if instr(snap(i,k) > 0 then hitlist.add snap(i,1) snap(i,1) 'create a collection with no duplicates exit for 'short circuit exit - any hit is good enough end ifnext 'now I can loop through my hitlist
