Finding with really "Finding"
Finding with really "Finding"
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.

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
  •  

 

 
DMCA.com