Find partial

[NDude]

Hi everybody,

I've been looking on the board how to do this but I can't quite find it.

I am trying to write a macro that will search for a certain value (located in sheet 2, A1) in sheet 2 (column A1:A500). I want it to copy all rows with that value. But the thing is I only want the rows where the beginning of the database matched the search criteria.

E.g.

Search criteria: ABC
Database list:
1) ABC
2) ABC
3) ABCD
4) ABCD
5) ZABC
6) 12ABC

I only want nr's 1-4 and not 5 and 6 (what find usually gives).

Any ideas?

NDude

 

[Juan Pablo González]

How about and advanced filter with a condition like

ABC*

or

=LEFT(A1,3)="ABC"

 

[Juan Pablo González]

Or even an Autofilter with "Begins with" and "ABC", then you could copy the visible rows only.

 

[Richie(UK)]

Hi NDude,

Welcome to the board.

Try this.<pre>
Sub findtext()
Dim FindMe As String, c As Range, TestArea As Range, firstaddress As String

FindMe = "ABC" & "*"
Set TestArea = ThisWorkbook.Sheets("Sheet2").Range("A1:A500")

With TestArea
Set c = .Find(What:=FindMe, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
firstaddress = c.Address
Do
c.Interior.ColorIndex = 4
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address<> firstaddress
End If
End With

Set TestArea = Nothing
Set c = Nothing

End Sub</pre>

HTH

EDIT: JP - you're too quick! :wink:

_________________<font color="blue"> «««<font color="red">¤<font color="blue"><font size=+1>Richie</font><font color="red">¤<font color="blue"> »»»</font>

</gif>
This message was edited by Richie(UK) on 2002-10-10 11:36

 

[NDude]

Everything works great... Thanks!!!