PDA

View Full Version : [SOLVED] vba Linear Sort help



byarbro
03-03-2015, 07:49 PM
Hello, Im trying to sort a group of numbers ascending through an array. It needs to get the numbers and then output them in another part of the sheet. I've tried to do it and it only prints out one of my numbers.


Sub sortyoung()


Dim young(1 To 5) As Single
Dim N As Single



For i = 1 To 5 Step 1
young(i) = Sheet1.Cells(2 + i, 4).Value

iMin = i
For L = (i + 1) To 5 Step 1
If young(L) < young(iMin) Then
iMin = L
End If

Next L
If iMin <> i Then
TEMP = young(i)
young(i) = young(iMin)
young(iMin) = TEMP

End If


Sheet1.Cells(2 + i, 6) = young(i)


Next i




End Sub

Paul_Hossler
03-04-2015, 07:27 AM
1. Welcome

2. You can use the [#] button to make [ CODE ] [ /CODE ] tags (without the spaces) and paste your code in between to make it format

3. I like to always use Option Explicit in each module to make sure I didn't mis-type something (check Help for details)

4. Suggest breaking the program flow into bit-size chunks for you and others to review (I have 3 below)

5. This follows using your technique. There are of course more 'Excel-like' ways of doing this



Option Explicit

Sub sortyoung()
Dim i As Long, j As Long
Dim young(1 To 5) As Single
Dim temp As Single


'load array
For i = LBound(young) To UBound(young)
young(i) = Sheet1.Cells(2 + i, 4).Value
Next i


'bubble sort array
For i = LBound(young) To UBound(young) - 1
For j = i + 1 To UBound(young)
If young(i) > young(j) Then
temp = young(j)
young(j) = young(i)
young(i) = temp
End If
Next j
Next i

'put in sheet
For i = LBound(young) To UBound(young)
Sheet1.Cells(2 + i, 6) = young(i)
Next i
End Sub

byarbro
03-04-2015, 12:58 PM
Thank you for the help. Can you explain what the LBound To Ubound does? I'm guessing it just tells where to start and where to end but I have not gone over that in my class yet.

Paul_Hossler
03-04-2015, 03:08 PM
Sure

The size of arrays can change or might be unknown when you write the macro so I never assume that they will start at 1 and end at 100

As a homework assignment play around with this demo sub and you should be able to work it out



Option Explicit
'Option Base 1 ' Uncomment this and see what happens to AnyArray LBound

Sub Bounds()

'three dimensional array
Dim MyArray3D(1 To 10, 15, 10 To 20) ' Declare array variables.

'one dimensional array
Dim AnyArray1D(99) ' assumes lower index = 0

MsgBox LBound(MyArray3D, 1) & " -- " & UBound(MyArray3D, 1)
MsgBox LBound(MyArray3D, 2) & " -- " & UBound(MyArray3D, 2)
MsgBox LBound(MyArray3D, 3) & " -- " & UBound(MyArray3D, 3)

MsgBox LBound(AnyArray1D) & " -- " & UBound(AnyArray1D)
End Sub