With Loop + Find Method = Confused

**hurricanopotamus** · 02-23-2010, 01:46 PM

Greetings!

A couple years ago I got my feet wet with VBA in Excel 2003, and had a good time of it. I'm a big fan of With and Find, we used to get along well. However, I've since upgraded to 2007, and am having trouble recollecting my former know-how to get even simple macros built again.

The current problem is this: I have a very large worksheet (row count maxed in 2007, and then some), for which I need to do the following: search column A for a string that will occur many times, and then check the 10 cells that follow in its row for negative values, dropping some sort of indicator in the 11th (shading it red or something would be fine). An additional bonus would be if the 10 cells that possibly contain a negative could be summed (the sum could serve as the indicator?). If no negative is found, nothing need be done, and the macro should chug along searching A for the next reference to this string.

My hope was to do a sort of "With Range("A:A"), .Find("MyString")", save position as StartPos, do the 10-cell row checking in a nested IF or For (though the For would take a long while, checking each cell individually), then doing a .FindNext after StartPos until = StartPos (does .FindNext loop back to the top?). The formatting of the indicator cell in the 12th cell in each relevant row doesn't really matter, it's more just for jumping to critical rows.

Is any of this possible? Efficiency would be nice, as I could absolutely code a For loop to run through the million-plus rows, but I expect about 130,000 hits on MyString in column A, so it would take quite a while.

Thanks in advance for any help offered!

**JBeaucaire** · 02-23-2010, 02:02 PM

With that much crunching, perhaps it would be better to use worksheet formulas to do the heavy lifting?

If you can construct the formula to fully evaluate a single row of data, you can use your .FIND method to quickly spot the rows and drop the formula in...

...OR, you can use the AUTOFILTER on the appropriate column to filter the data to only show the 130,000 matching rows, and insert the formula into the appropriate column for all visible rows...all at once. Could be significantly faster over all.

**hurricanopotamus** · 02-23-2010, 02:09 PM

JBeaucaire, your response has made me aware that I am overcomplicating the issue with my desire to get coding again! You're absolutely right that worksheet functions are appropriate for my goal, and the solution is ridiculously simple:

=IF(A10="MyString",COUNTIF(B10:K10,"<0"),""), fill down, then another COUNTIF to sum if any of those are >0, indicating negative values.

The autofilter will also greatly increase the readibility of such a large dataset, so I appreciate that advice as well!

Thanks very much!