Finding with really "Finding"

[jrnyman]

One of the things that slows down all my macros is using the find command. For example, I need to find the name of a company in column A and then copy data in column C to another worksheet. Is there a way to do this other than:

Columns("A:A").Select
Selection.Find(What:=Company, After:=ActiveCell, LookIn:=x1Values, LookAt:=x1Whole, SearchOrder:=x1ByColumns, SearchDirection:=x1Next, MatchCase:=False).Active
ActiveCell.Offset(0,2).Copy
Sheets("Sheet2").Select
Columns("A:A").Select
Selection.Find(What:=Company, After:=ActiveCell, LookIn:=x1Values, LookAt:=x1Whole, SearchOrder:=x1ByColumns, SearchDirection:=x1Next, MatchCase:=False).Active
ActiveCell.Offset(0,1).Select
ActiveSheet.Paste


Thanks for the help.

 

[Legacy 98055]

Hi
When possible, I try to remove "Select" from any of my code. For example:

Columns("A:A").Find(What:=Company, After:=ActiveCell, LookIn:=x1Values, LookAt:=x1Whole, SearchOrder:=x1ByColumns, SearchDirection:=x1Next, MatchCase:=False).Active
ActiveCell.Offset(0,2).Copy
Sheets("Sheet2").Columns("A:A").Find(What:=Company, After:=ActiveCell, LookIn:=x1Values, LookAt:=x1Whole, SearchOrder:=x1ByColumns, SearchDirection:=x1Next, MatchCase:=False).Active
ActiveCell.Offset(0,1).Select
ActiveSheet.Paste

I've not tested your code, but give it a shot. Mess around with it alittle. Turn off screenupdating, disable events.
Put these at the beginning of your macros when applicable:
Application.ScreenUdating = false
Application.EnableEvents = false
As a good practice, set these back to treu at the end of your procedure...

Just a few ideas...
Tom

 

[jrnyman]

I've tried pulling out as many "Selects" as i can, but it doesn't seem to work when I do that with Find commands. If I try something like:

Columns("A:A").Find (What:=Company...)

It never seems to work unless the column is selected first. Not sure why. It works with Sort, replace, pastspecial, and all the others I use, but not find. Thanks for the EnableEvents tip.