+ Reply to Thread
Results 1 to 8 of 8

Searching for a value but skip the first found value.

  1. #1
    Registered User
    Join Date
    07-07-2008
    Location
    Bath
    MS-Off Ver
    Microsoft Office 2007
    Posts
    42

    Exclamation Searching for a value but skip the first found value.

    Hi all,

    I have a macro with what I can search and works as I would like:

    Range("A1").Select
    Cells.Find(What:="Site 125", After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Range(Cells(1, 5), Cells(8, 3)).Select
    Selection.Copy
    Windows("report_template.xls").Activate
    ActiveSheet.Next.Select
    ActiveSheet.Next.Select
    ActiveWindow.SmallScroll Down:=18
    Range("B1395:D1395").Select
    ActiveSheet.Paste
    Sheets("Dashboard").Select
    Application.CutCopyMode = False
    Range("A1").Select
    BUT I would like to be able to skip the 1st found value and copy only the second one. I don't know if that's possible or not.

    Thanks for your help,
    Regards,
    George
    Last edited by george1982; 06-14-2009 at 07:40 AM.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,485

    Re: Searching for a value but skip the first found value.

    You can use the FindNext method

    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    07-07-2008
    Location
    Bath
    MS-Off Ver
    Microsoft Office 2007
    Posts
    42

    Re: Searching for a value but skip the first found value.

    Hi Andy,

    Thank you for the quick reply. I've made a mistake, copied in the wrong code:

    Range("A1").Select
    Cells.Find(What:="Reports 125", After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(6, 0).Select
    ActiveCell.Range(Cells(1, 5), Cells(8, 3)).Select
    Selection.Copy
    Windows("report_template.xls").Activate
    ActiveSheet.Next.Select
    ActiveSheet.Next.Select
    ActiveWindow.SmallScroll Down:=18
    Range("B1395:D1395").Select
    ActiveSheet.Paste
    Sheets("Dashboard").Select
    Application.CutCopyMode = False
    Range("A1").Select
    My list has also other such searches, but just recenlty I've got the problem that I have 2 report which have the beginning 'reports 125' e.g.

    Report 125a
    Report 125b

    and with the above code all the time he will find just the 1st one.

    Thanks,
    George

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,485

    Re: Searching for a value but skip the first found value.

    Then why not change the What text?

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-07-2008
    Location
    Bath
    MS-Off Ver
    Microsoft Office 2007
    Posts
    42

    Re: Searching for a value but skip the first found value.

    Hi Andy,

    I'm just to tired ... again you are totally right. But I've had a bad example above.

    This is what I have to search for:

    site
    site 1

    But whenever I search for 'site' it pick up the 'site 1' because that's first in the report (what I can't change). I don't know if it can be set up to search for the exact same text as given ...

    Thanks,
    George

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,485

    Re: Searching for a value but skip the first found value.

    Your problem is that you are searching for part of a cells content so a cell with "site" and "site 1" as returned references is completly valid.

    Do you really need to search on part of a cell? What other text is in the cell?

    If the cell does contain other text then you will need to do more coding to confirm the returned cell is the one you want. You have the reference to rngFind that you can then check for other information. For example what follows the "Site " text? maybe you can check for it not being a number.

  7. #7
    Registered User
    Join Date
    07-07-2008
    Location
    Bath
    MS-Off Ver
    Microsoft Office 2007
    Posts
    42

    Re: Searching for a value but skip the first found value.

    Hi Andy,

    I've been afraid of this.

    Sadly I have text and a large sheet ... more the 100 sites.

    Some of them have the text afterwords 'Site GM' but it's always text and also not the same.

    The bad news is that I'm not really a VB programmer ... just a novice.

    Any help is much appreciated.

    Regards,
    george

  8. #8
    Registered User
    Join Date
    07-07-2008
    Location
    Bath
    MS-Off Ver
    Microsoft Office 2007
    Posts
    42

    Re: Searching for a value but skip the first found value.

    I managed to find a work around. It mainly just something logical then technical.

    I wrote a macro that searches and replaces the specified values. In this case it would search for 'site 1' and then replace it with '1 site', this way my macro find the right fields.

    If somebody has a better idea please let me know.

    Cheers,
    George

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1