Excel 2007 : Sorting arrays in vba for excel 2007

[sunita]

Hello!

I have been struggling with this all afternoon, and was hoping someone could help!

I am trying to sort an array (size is 3 x 10) with the following data

The first column has a country name, and the second column has total score. The array is a Variant type (as I will have both the name and data); and as per example below, I have loaded the data so that Cell (1,1)= Australia, and Cell(2,1)= "27" therefore there is no need to transpose when printing to screen.

I want to sort the array by total score, so that the country with the highest score is in the first row (and the one with the lowest score is in the last row of the array).

Secondly, once I have sorted the array, I want to assign a rank to each country based on whether it is in the top third of scores, middle third of scores or bottom third of scores.

Lastly, I want to print this array onto a new worksheet for the user to see.

Example of my array before sort:
Country name Score
Australia 27
Austria 40
Benin 3
Canada 35

Example of array after the sort

Austria 40 ..... 1
Canada 35 ...... 2
Australia 27 ....... 2
Benin 3 ....... 3

Thanks so much! I am a newcomer to VBA (and computer programming) so appreciate all the help!

Cheers!
Sunita

[DonkeyOte]

Why not just paste the unsorted variant array to the worksheet, sort the resulting range object and apply the rank based on row ?

edit: might be worth adding the link to Chip Pearson's overview of the subject: http://www.cpearson.com/excel/SortingArrays.aspx

[sunita]

Hi!

Thanks so much for your quick response.

The problem is, I do not know the code for pasting the array to a new worksheet... can you advise?

Sunita

[DonkeyOte]

It's all a bit vague to provide anything useful I'm afraid.

In generic terms, assume vArray is your 2-Dimensional Variant Array:

Please Login or Register  to view this content.

[sunita]

Thanks for this! I will try it out and let you know how it works.

Cheers,
Sunita

[sunita]

Hi,

I tried the code above (with some modifications). It worked perfectly for printing the array to a new worksheet.

However, I encountered an error when I got to the .Sort line of the code... error 448 "named argument not found". I guess this is referring to the word 'Range' I used in my code, though I am not sure. Can anyone suggest a way around this?

Private Sub Evaluate_data_Click()
Dim i As Integer
Dim Msg As String
Dim count As Integer
count = 1

With Sheets.Add
.Cells(1, 1) = "Country"
.Cells(1, 2) = "Total score"
.Cells(1, 3) = "Tier"

For i = 1 To 9
If Not rankingarray(2, i) = 0 Then
.Cells(count + 1, 1) = rankingarray(1, i)
.Cells(count + 1, 2) = rankingarray(2, i)
count = count + 1
End If
Next i

.Sort Key1:=.Range("B2:B11"), Order1:=xlDescending, Key2:=.Range("A2:A11"), Order2:=xlAscending

End With

Unload UserForm4
End Sub

Thanks a million!
Sunita
P.S.-- this is my first time using such help forums, and am very pleasantly surprised at how helpful people are

[romperstomper]

You need to sort the range, not the sheet:

Please Login or Register  to view this content.

for example. Otherwise you are referring to the new Sort object in 2007 and your syntax is incorrect for that.

[sunita]

Yep tried this out-- it works.
Thanks for this!