For/Each w/Find optimization

[NicholasP]

OK...one more piece of code is running super slow...I'm stepping through a range cell by cell (which I DON'T like, as the range is ~7000 cells) and for each cell I'm looking it up in a different range that contains lookup values that replace the abreviations in the range with 7000 cells...Any thoughts? This code really bogs down on the cell.offset(0,1) = c.value bit....

Again, I appreciate any assistance given -- Oh, and there are probably 30 or 40 abbreviations, hence the lookup table...

Thanks
Nick

For each cell in Range(Cells(4,16),Cells(4,7000))
set c = rng.Find(cell, , ,  xlwhole)
     If not c is nothing then
          cell.offset(0,1) = c.value
     End if
Next

 

[jbeaucaire]

Think of doing this with a VLOOKUP formula in an empty cell. If you can make the column work for bringing up the required values, you can:
1) insert that formula into an empty column the size of your range ALL AT ONCE
2) then Copy the range as a whole
3) PasteSpecial/Values this new column of data into position ALL AT ONCE
4) Then delete the column of formulas you added.

Done. 4 steps. No Loop.

If you show more of your code or explain where the FIND is being done, we can help.

 

[NicholasP]

yeah...the vlookup is a good idea, but I have some abbreviations that are good for a couple of different categories...

example

OILS is an abbreviation for a class which can represent both Gas Pipelines (where I would need to insert "Natural Gas" instead) and OILS represents Oil Service, (where I would need to insert "Energy" instead)... I think that's the reason I went the route I did....

 

[jbeaucaire]

There's nothing in your code that takes that into account. I'm strictly talking about replacing your looping logic with a one-step placement of formulas.

If you have some additional "hoops" (not indicated by your sample macro above), it's nothing the formula you insert can't handle. Truly. Just need to think it through. If you're evaluating by one column only, then OILS is going to give you the wrong results with your current macro anyway, so whatever IT is doing to keep from making an error, then the formula could do the exact same thing.

The answer is still going to be skipping the loop in favor a well-constructed formula. If you want help with that, explain the "exceptions" and we can put it together. Post up a clear sample of data and how the differentiation occurs on these duplicates....and we'll get it done.