For/Each w/Find optimization

NicholasP

Active Member
Joined
Nov 18, 2006
Messages
291
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


Code:
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
 
Last edited:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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.
 
Upvote 0
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....
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,373
Messages
6,171,693
Members
452,418
Latest member
kennettz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top