Sorting procedure doesn't work

Question

I'm trying to sort a column of 7 values starting in cell A40, and ending in A46. I am using a bubble sort and I create two procedure. However, when I execute, VBA tells me the subscript is out of range... Would anyone be able to tell me where is the problem in my code, please?

Sub dort()

    Dim plaga() As Variant
    plaga = Worksheets("Sheet3").Range("A40:A46").Value


    Call tri1(plaga)

    Dim Destination As Range
    Set Destination = Worksheets("Sheet3").Range("C40")
    Destination.Resize(7, 1).Value = plaga

End Sub

Sub tri1(plaga As Variant)


    Dim ligne_Deb As Long
    Dim ligne_Fin As Long

    ligne_Deb = LBound(plaga)
    ligne_Fin = UBound(plaga)

    Dim i As Long, j As Long
    Dim tmp As Long

        For i = ligne_Deb To ligne_Fin - 1
        For j = ligne_Fin To i + 1 Step -1
            If plaga(j) < plaga(j - 1) Then
            tmp = plaga(j)
            plaga(j) = plaga(j - 1)
            plaga(j - 1) = tmp
            End If
        Next j
        Next i

End Sub

why you sort this way instead of using Excel built-in Sort object? keep in mind that your procedure is inefficient — Kazimierz Jawor
– Kazimierz Jawor, Commented Apr 12, 2013 at 10:23

Community · Accepted Answer · 2018-05-08 21:48:18Z

2

Would anyone be able to tell me where is the problem in my code, please?

plaga = Worksheets("Sheet3").Range("A40:A46").Value

When you store range in an array, it is not a single dimension array.

Change your code to

plaga(j,1)

Notice the ,1. Incorporate that every where.

For example

For i = ligne_Deb To ligne_Fin - 1
    For j = ligne_Fin To i + 1 Step -1
        If plaga(j, 1) < plaga(j - 1, 1) Then
            tmp = plaga(j, 1)
            plaga(j, 1) = plaga(j - 1, 1)
            plaga(j - 1, 1) = tmp
        End If
    Next j
Next i

Interesting read

edited May 8, 2018 at 21:48

CommunityBot

11 silver badge

answered Apr 12, 2013 at 10:21

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Siddharth Rout Over a year ago

I just tested the code and it works just fine. Replace your FOR loop with my FOR loop

seigna Over a year ago

Could you just please tell me why the array is not one-dimension even if I took a range than it composed of several rows but just one column?

Siddharth Rout Over a year ago

See the link that I added at the bottom of my post ;)

Siddharth Rout Over a year ago

Gr8 :) Now the main question. Why bubble sort and not the Excel's inbuilt sort? Is your data stored as alpha numeric data?

seigna Over a year ago

No, I would have liked to use an excel inbuilt sort, but I am required to write the algorithm myself, as it is for a project...

|

Collectives™ on Stack Overflow

Sorting procedure doesn't work

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related