Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Finding with really "Finding"

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •