sorting an array 1

[TRHorn]

I have built an array of ranges. Now I want to sort it by the value of one of its columns and then insert it back into the spread sheet.

I can't figure out how to sort an array of ranges by cell values though.

Any ideas?

 

[SkipVought]

Hi,

Any reason why you cannot insert back into the sheet and then SORT on the sheet, using the SORT feature in Excel?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[JTBorton]

I posted some sorting code in discussion not too long ago. See a discussion titled Sorting of Date.

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[TRHorn]

I could do that, I need to read up on the sort feature. I only started programming heavily in the past couple of days.

Here is the problem with putting the info back on the sheet.

Case "show all"
Dim H As Integer
'selecting cell A1
wksSort.Cells(1, 1) = ""
'attempting to put the range on the sheet.

For H = 1 To sngTot
wksSort.Range(Cells(H, 1), Cells(H,_ 9)).Value = rngShowAllArray(H)
Next

I can't seem to set my array "rngshowallarray(H)" equal to the range specified. I get an error 1004

 

[JTBorton]

TRHorn,

First, you have an underscore before your 9. Unless you were dropping down to a new line and just forgot to take it out before you posted online, that is going to cause an issue.

The way you have your code set up now, every single cell in the range will have the exact same number. For example, if we modify your code for simplicity (and because I don't know what your other variables are),

For H = 1 To 12
    ActiveSheet.Range(Cells(H, 1), Cells(H, 9)).Value = H
Next H

running this code will produce
1 1 1 1 1 1 1 1
2 2 2 2 2 2 2 2
3 3 3 3 3 3 3 3
etc..

Another point of interest I see is when you declare your For loop. If sngTot is a single data type, which the prefix implies, the loop will only accept the integer portion of it. For example, if sngTot =7.56, the loop will ignore the decimal and accept the 7 as a parameter.

If you are trying to extract data out of an array to correspond with each cell, try something like this:

For H = 1 To sngTot
        ActiveSheet.Cells(Row, H) = myArray(H)
Next H

Or, if you are trying to match up with 2D arrays, use you can use nested loops.

For H = 1 To sngTot
    For I = 1 To 9
        ActiveSheet.Cells(H, I) = myArray(H, I)
    Next I
Next H

The outside loop will iterate through each row, while the inside loop will iterate through each column.

The other way to pull array data into cells without loops is by calling a function and sending an array of cells. This is done by selecting a group of cells, entering a formula, and then pressing SHIFT + ENTER (or maybe there is a CTRL button in there too, I forget). The function will execute and fill the array data into the cells.

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[TRHorn]

I should have done it as a 2D collection from the beginning that makes it alot easier on the loading, and rewrite.