#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 80
Default Sorting By Value

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200712/1

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,180
Default Sorting By Value

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.
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 80
Default Sorting By Value

The problem is sometimes I don't have names in Order.

Herbert Seidenberg wrote:
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.


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200712/1

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,180
Default Sorting By Value

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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorting VLookup vs Sorting SumProduct Lauren Excel Discussion (Misc queries) 1 August 21st 07 12:19 AM
Sorting Arun Kumar Saha Excel Worksheet Functions 1 July 4th 07 06:45 PM
Sorting bac Excel Discussion (Misc queries) 3 September 28th 06 10:19 PM
Sorting Newbeetle Excel Discussion (Misc queries) 7 September 24th 06 07:58 PM
Sorting: Sorting by the First Character dzuy Excel Discussion (Misc queries) 2 June 22nd 06 08:27 PM


All times are GMT +1. The time now is 10:09 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"