Ascending Order

Basic Exercise

Statement

Create a macro to organize the numbers in ascending order. Keep the original data unaltered.

Suggested Concepts:

Dim - Declares a variable.
Set - Assigns an object reference to a variable or property.
For Next - Repeats a group of statements a specified number of times.
If Else - Conditionally executes a group of statements, depending on the value of an expression.
CurrentRegion - Returns a range object that represents a region.
Cells - Allows VBA to interact with cells in the worksheet.

Remember that in most cases there is more than one way to solve the exercise.

Hint: Create a loop with another loop. If the variable of the second loop have a higher value than the variable of the first, substitute the variable.

Solved Exercise

Exercise Solution

We declare the variables

Dim i As Integer
Dim j As Integer
Dim temp As Integer

We copy the data to column C to keep the original data unaltered
```
Range("A3:A100").Copy Destination:=Range("C3:C100")
```
We define as the Rng object the data containing area using the CurrentRegion property
```
Set Rng = Cells(3, 3).CurrentRegion
```
We create two For Next loops, one within the other. While the first loop is on the i term, the second loop will run from the i + 1 term until the end of the list.
```
For i = 2 To Rng.Count
    For j = i + 1 To Rng.Count
```
We create an If Else conditional to determine that the numbers will change position every time that the number on the second loop is lower than the number in the first loop
```
If Rng.Cells(j) < Rng.Cells(i) Then
    temp = Rng.Cells(i)
    Rng.Cells(i) = Rng.Cells(j)
    Rng.Cells(j) = temp
End If
```
When we apply this logic for the whole list, the list will be organized in ascending order
```
    Next j
Next i
```

Consolidated Answer

    Sub Solution()

    Dim i As Integer
    Dim j As Integer
    Dim temp As Integer

    Range("A3:A100").Copy Destination:=Range("C3:C100")

    Set Rng = Cells(3, 3).CurrentRegion
        For i = 2 To Rng.Count
            For j = i + 1 To Rng.Count
            If Rng.Cells(j) < Rng.Cells(i) Then
                temp = Rng.Cells(i)
                Rng.Cells(i) = Rng.Cells(j)
                Rng.Cells(j) = temp
            End If
            Next j
        Next i

    End Sub

Show Answer

Download exercise Download answer

SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.

Excel ® is a registered trademark of the Microsoft Corporation.

INTRODUCTION

BASIC

INTERMEDIATE

ADVANCED