PDA

View Full Version : Store ordered list in string.



realitybend
07-23-2008, 11:40 AM
I'm stumped and maybe you can help me out.

I have data in column A that is out of order.
I need some vba that computes what order it should be in (numerically, increasing).
It needs to store this order by row number, e.g. row 3 comes first, then row 5, then row 4.

Eventually, I'm going to need to access this data and use it to copy that row. I should be able to do this myself (hopefully).

Thanks for your help.

mdmackillop
07-23-2008, 11:59 AM
Option Base 1
Sub GetOrder()
Dim arr()
x = Range("Data").Cells.Count
ReDim arr(Range("Data").Cells.Count)
For i = 1 To x
arr(i) = Application.Rank(Cells(i, 1), Range("Data"))
Cells(i, 2) = arr(i)
Next
End Sub

mdmackillop
07-23-2008, 12:03 PM
for reverse order
arr(i) = Application.Rank(Cells(i, 1), Range("Data"), 1)

realitybend
07-23-2008, 12:31 PM
If it's not too much of a bother, could you explain the logic behind this part of it? Thanks.

For i = 1 To x
arr(i) = Application.Rank(Cells(i, 1), Range("Data"))
Cells(i, 2) = arr(i)
Next

mdmackillop
07-23-2008, 12:39 PM
The rank is the order of the cell in the range, ascending or descending. This is being stored in an array for some use, and for debug purposes, writes the rank to the sheet.

realitybend
07-23-2008, 01:14 PM
I changed it to this to accomodate data starting in row 6

x = Range(MyRange).Cells.Count
ReDim arr(Range(MyRange).Cells.Count)
For i = 6 To x + 6
arr(i) = Application.Rank(Cells(i, 1), Range(MyRange), 1)
Cells(i, 2) = arr(i)
Next

but get subscript out of range here
arr(i) = Application.Rank(Cells(i, 1), Range(ChargeRange), 1)

mdmackillop
07-23-2008, 01:36 PM
Sub GetOrder()
Dim arr()
x = Range("Data").Cells.Count
ReDim arr(Range("Data").Cells.Count)
For i = 1 To x
arr(i) = Application.Rank(Range("Data")(i), Range("Data")) + 5
Cells(i + 5, 2) = arr(i)
Next
End Sub

realitybend
07-23-2008, 01:38 PM
It still gets stuck on this:

arr(i) = Application.Rank(Cells(i, 1), Range(MyRange)) + 6

mdmackillop
07-23-2008, 01:42 PM
My mistake. I changed that bit and reposted.

mdmackillop
07-23-2008, 01:50 PM
A little better using the Range to set the start row

Option Base 1
Option Explicit
Option Base 1
Sub GetOrder()
Dim arr()
Dim Rng As Range
Dim Rw As Long, x As Long, i As Long

Set Rng = Range("Data")
x = Rng.Cells.Count
Rw = Rng(1).Row - 1
ReDim arr(x)

For i = 1 To x
arr(i) = Application.Rank(Rng(i), Rng) + Rw
Cells(i + Rw, 2) = arr(i)
Next

End Sub

realitybend
07-23-2008, 01:58 PM
Thanks! It works. Now to use it to copy those rows... I'll probably have to ask for some more help as time goes on.

realitybend
07-24-2008, 10:48 AM
How would I make it skip the cell (and array entry) if the font is red?

realitybend
07-24-2008, 11:25 AM
I added just a simply copy to it, and tried it out, thinking it would copy in order. My code is below:
For i = 1 To x
Range("A" & arr(i) & ":II" & arr(i)).Copy
Next

Under copied output, you'll see the results: not in order. Help!

realitybend
07-24-2008, 01:14 PM
I see what's wrong, and I think I know what must be done to fix it, but I don't know how to code it.

I'm going to need the array to be multidimensional (two columns), and with the row number in the first, and the one that has been ranked in the second. Then it will have to be sorted by the second column, in ascending order, in order to tell which row to copy first.

Am I on the right track, and is anyone willing to help?

mdmackillop
07-24-2008, 01:16 PM
'Write data
For i = 1 To x
Cells(10 + arr(i), 5) = Range("Data")(i)
Next

realitybend
07-31-2008, 11:09 AM
I have to copy the row. Here's the first part of code:

Range(Cells(arr(i), 5), Cells(arr(i), 255)).Value =

but what should it equal to copy in order?

Thanks.