ExcelBanter - Check cell range for a fragment

This will ask you for an input, and then copy/paste results to a Sheet2.

Sub CopyCA()

Dim DestSheet As Worksheet
Set DestSheet = Worksheets("Sheet2")

Dim sRow As Long
Dim dRow As Long
Dim sCount As Long
sCount = 0
dRow = 0

myword = InputBox("Enter items to search for.")

For sRow = 1 To Range("A65536").End(xlUp).Row

If Cells(sRow, "A") Like "*" & myword & "*" Then
sCount = sCount + 1
dRow = dRow + 1
Cells(sRow, "A").Copy Destination:=DestSheet.Cells(dRow, "A")
End If
Next sRow

MsgBox sCount & " Significant rows copied", vbInformation, "Transfer Done"

End Sub

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

"Don Guillett" wrote:

That's "The DonALD" ala Trump

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"NewsOfTheDay" wrote in message
...
Don,

Thank you Thank you and Excellent you are indeed The DON! perfect. Have a
good evening your effort is well appreciated. It works exaclty as
required.

Regards - Terry

"Don Guillett" wrote in message
...
Sub FindVehicle()
Dim parnum As String
Dim myfind As Variant
parnum = InputBox("Enter Partial ID ie:123x")
Set myfind = Range("Vehicle_Reg").Find(parnum, _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext)
If Not myfind Is Nothing Then
myfind.Interior.ColorIndex = 6
MsgBox "Found at " & myfind.Address
End If
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
I would probably use vba FIND instead but what about partial matches. If
desired, send your file to my address below along with this msg, a clear
explanation and before/after examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"NewsOfTheDay" wrote in message
...
Hi,

I would like to find a way to search through an experimental list of
car registration, which is alphanumeric. What I want to do is use VBA
to find the registration number using only part of the registration
plate number which will be inputted by the user into an input message
box.

I have got an Input Box in place for the user to enter the partial
registration plate number. I want excel to find the cell location from
the list that hold the fully matching registration number, and once
it's found it, to then fill the cell with the colour it green. I'm not
sure where to put this line

If myCell Like ("*" & junk & "*") Then

Here is the full code below;

Sub getValidVehicle()

Dim thisReg As Variant

Dim myCell As Object

Dim Vehicle_Reg As Object

Dim isfound As Boolean

Dim junk As Object 'fragment of registration holder

Worksheets("Sheet1").Select

isfound = False

Do Until isfound

thisReg = InputBox(Prompt:="enter registration")

For Each myCell In Range("Vehicle_Reg")

If myCell Like ("*" & junk & "*") Then 'found it

myCell.Interior.ColorIndex = 10

isfound = True

MsgBox "found at" & myCell.Address

End If

Next

Loop

End Sub

(thank you) Terry