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
> (E-Mail Removed)
> "NewsOfTheDay" <(E-Mail Removed)> wrote in message
> news:OSb9hH$(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:%23mioH4%(E-Mail Removed)...
> >> 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
> >> (E-Mail Removed)
> >> "Don Guillett" <(E-Mail Removed)> wrote in message
> >> news:ubZ$(E-Mail Removed)...
> >>>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
> >>> (E-Mail Removed)
> >>>
> >>> "NewsOfTheDay" <(E-Mail Removed)> wrote in message
> >>> news:%(E-Mail Removed)...
> >>>> 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
> >>>>
> >>>>
> >>>
> >>
> >
> >
>
>
Similar Threads
