Searching for a value but skip the first found value.

**george1982** · 06-14-2009, 07:02 AM

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

**Andy Pope** · 06-14-2009, 07:23 AM

You can use the FindNext method

Please Login or Register  to view this content.

**george1982** · 06-14-2009, 07:55 AM

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

**Andy Pope** · 06-14-2009, 07:58 AM

Then why not change the What text?

Please Login or Register  to view this content.

**george1982** · 06-14-2009, 08:06 AM

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

**Andy Pope** · 06-14-2009, 08:10 AM

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.

**george1982** · 06-14-2009, 08:21 AM

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

**george1982** · 06-14-2009, 09:35 AM

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