# Array Find and Sort question

• ### Question

• Hi

These question relate to a one dimensional array - say 100 unique items.

Is ther a simple way to sort the items in an array?

Is there a simple to find a particular occurrence in an array?

I know both of the these can be done by looping, but I wondered if there was a more elegant way?

Many thanks

Peter

Friday, July 29, 2016 8:59 AM

• I know both of the these can be done by looping, but I wondered if there was a more elegant way?

Hi Peter,

if you (resp. your VBA code) doesn't loop through the items and perform a sort, you have to push all items onto the stack and call an external source that does the loop and performs the sort.

It is not possible to sort without a loop!

So IMHO the real question is what is easier and/or faster. And what kind of sort you want to use:
https://en.wikipedia.org/wiki/Sorting_algorithm

Long talk, short end: Use the code you already have. 100 items is so small... .-)

Andreas.

• Edited by Friday, July 29, 2016 10:14 AM
• Marked as answer by Friday, July 29, 2016 10:28 AM
Friday, July 29, 2016 10:13 AM
• Alternatively, you could store the array in a range of cells, then use the Sort method to sort it, or the Find method to search for a specific value. You'd still have to loop to read the array back in from the range. For an array declared as arr(1 To 100):

```    Dim i As Long
With Range("A1:A100")
' Store array in range
.Value = Application.Transpose(arr)
' Sort range
End With
' Read array back in
For i = 1 To 100
arr(i) = Range("A" & i).Value
Next i```

Regards, Hans Vogelaar (http://www.eileenslounge.com)

• Marked as answer by Friday, July 29, 2016 10:28 AM
Friday, July 29, 2016 10:25 AM

### All replies

• I know both of the these can be done by looping, but I wondered if there was a more elegant way?

Hi Peter,

if you (resp. your VBA code) doesn't loop through the items and perform a sort, you have to push all items onto the stack and call an external source that does the loop and performs the sort.

It is not possible to sort without a loop!

So IMHO the real question is what is easier and/or faster. And what kind of sort you want to use:
https://en.wikipedia.org/wiki/Sorting_algorithm

Long talk, short end: Use the code you already have. 100 items is so small... .-)

Andreas.

• Edited by Friday, July 29, 2016 10:14 AM
• Marked as answer by Friday, July 29, 2016 10:28 AM
Friday, July 29, 2016 10:13 AM
• Alternatively, you could store the array in a range of cells, then use the Sort method to sort it, or the Find method to search for a specific value. You'd still have to loop to read the array back in from the range. For an array declared as arr(1 To 100):

```    Dim i As Long
With Range("A1:A100")
' Store array in range
.Value = Application.Transpose(arr)
' Sort range
End With
' Read array back in
For i = 1 To 100
arr(i) = Range("A" & i).Value
Next i```

Regards, Hans Vogelaar (http://www.eileenslounge.com)

• Marked as answer by Friday, July 29, 2016 10:28 AM
Friday, July 29, 2016 10:25 AM
• thanks Andreas

kind regards

Peter

Friday, July 29, 2016 10:28 AM
• Thank you Hans - that is helpful.
Friday, July 29, 2016 10:28 AM