Sorting By Value

  • Thread starter saman110 via OfficeKB.com
  • Start date

S

saman110 via OfficeKB.com

Hello all,

I have a macro that looks in a col and and find what you asked and copy it to
sheet3. What I want is the macro instead of copying to sheet3 paste it on top
of sheet1 and delete the copied cell and bring the rows down. Something like
you sorting for value. for example, in sheet1 col. A has names and col.B is
number the program looks for a name and copy the whole row on top of the
sheet and delete the copied row.


Ex.

Sheet1:

sam 1
sam 2
sam 3
sam 4
sam 5
sam 6
sam 7
sam 8
sam 9
sam 10
sam 11
sam 12
tom 1
tom 2
tom 3
tom 4
tom 5
tom 6
tom 7
tom 8
tom 9
moe 1
moe 2
moe 3
moe 4
moe 5
moe 6
moe 7
moe 8
moe 9
moe 10


Results with the search of tom:

tom 1
tom 2
tom 3
tom 4
tom 5
tom 6
tom 7
tom 8
tom 9
sam 1
sam 2
sam 3
sam 4
sam 5
sam 6
sam 7
sam 8
sam 9
sam 10
sam 11
sam 12
moe 1
moe 2
moe 3
moe 4
moe 5
moe 6
moe 7
moe 8
moe 9
moe 10

Thank you.


Here is the VBA.

Public Sub FindStuff1()
Dim wksToSearch As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim wksDestination As Worksheet
Dim rngDestination As Range
Dim name As String
Dim col As String

col = InputBox("Enter A Column To Be Searched")
name = InputBox("Enter A Search Value")

Set wksDestination = Sheets("Sheet3") 'copy to
Set rngDestination = wksDestination.Range("A1")
Set wksToSearch = Sheets("Sheet1") 'Looks in
Set rngToSearch = wksToSearch.Columns(col) 'Looks in
Set rngFound = rngToSearch.Find(What:=name, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry. Not found"
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.EntireRow.Copy rngDestination
End If
End Sub
 
Ad

Advertisements

H

Herbert Seidenberg

In case you become interested in a
non-VBA solution:
Add a column with the formula
=IF(Subject="Tom",1,2)
and sort by that.
 
Ad

Advertisements

H

Herbert Seidenberg

The formula will work with scrambled data.
Try it.
It will list Tom first, then all the other guys
in the order they were found.
If you want to sort the other guys
use this formula:
=IF(Guys="Tom",1,Guys)

Or without formulas, Sort, then Cut
the Tom cells and Insert Cut Cells
at the top.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top