ExcelBanter - Advanced Sorting\VBA Code

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Advanced Sorting\VBA Code (https://www.excelbanter.com/excel-programming/333734-advanced-sorting%5Cvba-code.html)

Advanced Sorting\VBA Code

I have data which is exported into excel each month. I have been tryin
to write a macro in VBA which will find a certain item then copy th
row with this item on into another worksheet.

Could anyone help me with a generic code to find a certain text, the
copy the row with this text in to a certain row of another spreadsheet
Also is there a way to make it user friendly so you can type in the tex
and the row you want it in, in a user interface box?

Alternatively is there a way in excel or through code you can sort th
rows in a spreadsheet so that they appear in an order which is mor
advanced than ascending/descending. Eg I have rows with details of
apples, pears, oranges and bananas respectively and I want them to b
in the order pears, bananas, apples, oranges or whatever order
choose.

nb. I am using Excel 97.

Any help would be much appreciated!

Thank you in advance

--
fugfu
-----------------------------------------------------------------------
fugfug's Profile: http://www.excelforum.com/member.php...fo&userid=2495
View this thread: http://www.excelforum.com/showthread.php?threadid=38483

Advanced Sorting\VBA Code

Something like

Dim ans As String
Dim rng As Range
ans = InputBox("Enter value to find")
If ans < "" Then
On Error Resume Next
Set rng = ActiveSheet.Cells.Find(ans)
On Error GoTo 0
If Not rng Is Nothing Then
rng.EntireRow.Copy Worksheets("Sheet2").Range("A10")
End If
End If

--
HTH

Bob Phillips

"fugfug" wrote in
message ...

I have data which is exported into excel each month. I have been trying
to write a macro in VBA which will find a certain item then copy the
row with this item on into another worksheet.

Could anyone help me with a generic code to find a certain text, then
copy the row with this text in to a certain row of another spreadsheet.
Also is there a way to make it user friendly so you can type in the text
and the row you want it in, in a user interface box?

Alternatively is there a way in excel or through code you can sort the
rows in a spreadsheet so that they appear in an order which is more
advanced than ascending/descending. Eg I have rows with details of,
apples, pears, oranges and bananas respectively and I want them to be
in the order pears, bananas, apples, oranges or whatever order I
choose.

nb. I am using Excel 97.

Any help would be much appreciated!

Thank you in advance.

--
fugfug
------------------------------------------------------------------------
fugfug's Profile:
http://www.excelforum.com/member.php...o&userid=24950
View this thread: http://www.excelforum.com/showthread...hreadid=384833

Advanced Sorting\VBA Code

This is an answer to the third part of your post about sorting in an
order that is not alphabetical.

Assign your apples, pears and oranges to an array. You can use the
'redim' statement to make the array a dynamic array. Make sure that
there is only one entry for each item in this array and assign them in
the order that you would like them to appear in your ordered list.

Eg:

Code:
--------------------

Dim Object_Array(5) as String
'*
Object_Array(0) = "Oranges"
Object_Array(1) = "Apples"
Object_Array(2) = "Pears"
Object_Array(3) = "Bread Crumbs"
Object_Array(4) = "Raspberry"
Object_Array(5) = "Blueberry"

--------------------

Load your input data into a second array. This example assumes that
you are reading from a file. You can change the type to store other
items as well

Eg:

Code:
--------------------

Type Sort_Data
Order as Integer
Value as String
End Type
'*
Redim Data_Array(0) as Sort_Data
Dim Array_Count as Integer
'*
Array_Count = 0
While not eof(#1)
Line Input #1New_Data
I = 0
While Object_Array(I) < New_Data
I=I+1
Wend
Data_Array(Array_Count).Order = I
Data_Array(Array_Count).Value = New_Data
Array_Count = Array_Count + 1
Redim Preserve Data_Array(Array_Count) as Sort_Data
Wend

--------------------

Initialise a third array which is a series of integers. When we sort
the array, we don't move the actual data_array components as moving
strings/types etc is for more expensive in terms of processing power
than moving an integer

Code:
--------------------

Redim Sort_Ptrs(Array_Count-1) as Integer
For I = 0 to Array_Count -1
Sort_Ptrs(I)=I
Next I

--------------------

Now for the sort.

You don't give any indication of the size of the data that you are
sorting. The reason that this is important is that the amount of time
taken by the various sorts varies from Log2n as the slowest type to
faster than this(!!!!) for other types of sort.

If you are sorting only a few records then use the simplest of sort
which is the bubble sort. (I'm not going to code these - check on the
web for the source). For a few hundred records upto about 1,000 you
can use an intelligent bubbles sort which records the last record moved
and then only sorts upto that record on each iteration.

For 5000 to 50,000 records use a shell sort. This is an early form of
binary sorting.

For more than 50,000 records use C.A.R Hoares Quick Sort. This is a
sophisticated sort routine that is extrememly fast.

When you check the items to sort, your statement should compare

Code:
--------------------

Data_Array(Sort_Ptrs(I)).Order

--------------------

If you wish to sort the data in alphabetical order compare

Code:
--------------------

data_Array(Sort_Ptrs(I)).Value

--------------------

And when you swap pointers, you swap Sort_Ptrs(I) rather than the
Data_Array subscripts.

To then print your list out in sorted order, you do something like:

Code:
--------------------

For I = 0 to UBound(Sort_Ptrs)
Debug.Print Data_Array(Sort_Ptrs(I)).Value
Next I

--------------------

Regards

Rich

--
Rich_z
------------------------------------------------------------------------
Rich_z's Profile: http://www.excelforum.com/member.php...o&userid=24737
View this thread: http://www.excelforum.com/showthread...hreadid=384833

Advanced Sorting\VBA Code