Store ordered list in string.

[realitybend]

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]

[VBA]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
[/VBA]

[mdmackillop]

for reverse order
[VBA]arr(i) = Application.Rank(Cells(i, 1), Range("Data"), 1)[/VBA]

[realitybend]

If it's not too much of a bother, could you explain the logic behind this part of it? Thanks.

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

[mdmackillop]

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]

I changed it to this to accomodate data starting in row 6

[VBA] 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[/VBA]

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

[mdmackillop]

[VBA]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
[/VBA]

[realitybend]

It still gets stuck on this:

[vba] arr(i) = Application.Rank(Cells(i, 1), Range(MyRange)) + 6
[/vba]

[mdmackillop]

My mistake. I changed that bit and reposted.

[mdmackillop]

A little better using the Range to set the start row
[VBA]
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
[/VBA]

[realitybend]

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]

How would I make it skip the cell (and array entry) if the font is red?

[realitybend]

I added just a simply copy to it, and tried it out, thinking it would copy in order. My code is below:
[VBA] For i = 1 To x
Range("A" & arr(i) & ":II" & arr(i)).Copy
Next[/VBA]

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

[realitybend]

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]

[VBA] 'Write data
For i = 1 To x
Cells(10 + arr(i), 5) = Range("Data")(i)
Next[/VBA]

[realitybend]

I have to copy the row. Here's the first part of code:

[VBA]Range(Cells(arr(i), 5), Cells(arr(i), 255)).Value =[/VBA]

but what should it equal to copy in order?

Thanks.