Solved: Find cell containing PART of string

[maxhayden]

Hello all,

Rows of data looking like this:

Ref Name Date
123456/2009/INS Joe Bloggs 02/02/2009

I have a userform where you type in the first 6 digits of the "ref" number, it finds that particular row, copies it, and pastes it into a different sheet.

I have no problem doing most of this, using

[VBA]
y = UserForm1.TextBox1
Worksheets("instructionsreceived").Columns(1).Find(y).EntireRow.Copy
Selection.Paste
[/VBA]

But this won't work because I'm only typing in the first 6 digits.

Can I search for just part of the string (i.e. the first 6 digits). If so, how do I do this. I would really appreciate some help on this one.

Thanks in advance!

[mdmackillop]

Try
[VBA]Private Sub CommandButton1_Click()
y = UserForm1.TextBox1
For Each cel In Intersect(Worksheets(1).Columns(1), Worksheets(1).UsedRange)
If cel Like y & "*" Then
cel.EntireRow.Copy Selection
End If
Next
End Sub[/VBA]
or
[VBA]
Private Sub CommandButton1_Click()
y = UserForm1.TextBox1
Worksheets(1).Columns(1).Find(y, lookat:=xlPart).EntireRow.Copy Selection
End Sub

[/VBA]

[maxhayden]

Thanks for that. THe second one is a bit dangerous because it allows too much room for error. The first one is on the right track, but I need a bit more help:

With the following code:
[VBA]
If cel Like y & "*" Then
[/VBA]

The * could be either 2007, 2008, or 2009. Is there any way I could make it flexible to accept say "200" & "Number" & "ABC" for example?

[maxhayden]

I've just tried the 1st one and it is too slow for the 200000+ rows I have.

It looks like the 2nd one is the only option.

One more question though:

If I want to copy the cells in column A, C, and F, how do I do that instead of the whole row?

Thanks in advance by the way!

[mdmackillop]

How about Autofilter?
[vba]
Columns("A:A").Range("$A$1:$A$200000").AutoFilter Field:=1, Criteria1:="200*ABC"

[/vba]

Something like
[vba]
Sub Macro1()
Columns("A:A").Range("$A$1:$A$40").AutoFilter Field:=1, Criteria1:="200*ABC"
Set Rng = Cells(Rows.Count, 1).End(xlUp)
Set Rng = Union(Rng, Rng.Offset(, 2), Rng.Offset(, 5))
Rng.Copy Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1)
Columns("A:A").AutoFilter
End Sub

[/vba]

[maxhayden]

I can make the lookat:=xlPart work for me, but I'm not used to using the find function. (I normally run a loop - then I can easily select which cells I want to copy by using something like Cells(i, 1) or Cells(i +1).Copy etc etc.

[VBA]
Worksheets(1).Columns(1).Find(y, lookat:=xlPart).Columns(1, 3, 6).Copy
[/VBA]

The bit in bold doesn't work. I'm not sure how to code it because its not in Cells(i) format - and I'm a newbie!

If anyone can help that would be great.

[p45cal]

[vba]Worksheets(1).Columns(1).Find(y, lookat:=xlPart).Range("A1,C1,F1").Copy
[/vba]

[maxhayden]

Thanks for that!