Finding with really "Finding"

jrnyman

Board Regular
Joined
Mar 10, 2002
Messages
105
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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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