Module 6 - Collections and Loops
Lesson 6.2 - For Next Loops
Topic 6.2.3 - Counting Through Cells

For Next loops provide a convenient way to process a range of cells by counting through the rows and columns:

Cells

The Cells property of a range allows you to reference a single cell using a row number and a column number.

 

Files Needed

You can click here to download the file for this part of the lesson.

Completed Code

You can click here to download a file containing the sample code.

The Example Workbook

Extract and open the workbook linked to in the Files Needed section above.  You'll find a worksheet containing a list of character names:

List

The character names are divided into a title, first name and last name.

 

We'd like to join the title, first name and last name to create a full name for each character.

Counting Through Rows

We'll begin by creating a loop to count through the rows of data.  Open the VBE and find Module1.  Create a new subroutine called ProcessNameList and declare three variables as shown below:

Start of sub

We'll use the RowNum variable to count through the rows of the table. The other two variables will store the number of the first and last rows of data.

 

Next we can set the value of the FirstRow and LastRow variables.  We can see that the first row of data is in row 2 of the worksheet so we can assign a value to the FirstRow variable as shown below:

Assign value

Assign the value 2 to the FirstRow variable.

 

The last row of data is in row 11 but we don't know if the size of the list will change.  We can find the row number of the cell at the bottom of the list as shown below:

Last row

Refer to the Row property of the cell at the bottom of the list to assign a value to the LastRow variable.

 

Now we can configure a For Next loop to count through the rows of data as shown below:

For Next

For the original table of data, the loop will count from 2 to 11.

 

To demonstrate that the loop is working, we can add code to print the value of a cell to the Immediate window:

Debug

Use the Cells property and the RowNum variable to reference a cell in the correct row. We can use the value 1 to refer to a cell in the first column of the worksheet.

 

Run the subroutine and check the results in the Immediate window.  If you can't see this window, choose View | Immediate Window from the menu:

Results

The Immediate window will show a list of values from the first column in the worksheet.

 

Counting Through Columns

Next, we need to create a loop which counts through the columns for each row of data.  Start by declaring three new variables as shown below:

Column variables

The three new variables serve a similar purpose to the first three.

 

We can assign a value of 1 to the FirstCol variable as our data begins in the first column of the worksheet.  We can find the number of the last column of data using the Column property and the End property:

Assign values

Assign values to the FirstCol and LastCol variables as shown here.

 

We can now configure a second For Next loop within the first, as shown below:

Second loop

Insert the second loop within the first and ensure that the Debug.Print statement is inside the second loop.

 

Edit the Debug.Print statement and in the Cells property replace the number 1 with a reference to the ColNum variable:

Cells

The Cells property will use the value of each variable to refer to a different cell each time the loop is processed.

 

Clear the contents of the Immediate window and then run the subroutine again:

Results

The Immediate window will show a list of every value from the table of data on the worksheet.

 

Building a Full Name

Now that we can access the value of each cell we can add the code to build the full name for each character.  Start by declaring a String variable to hold the full name:

Declare variable

Declare this variable within the subroutine.

 

Now find the Debug.Print statement within the For Next loop and remove it.  Replace the deleted statement with one which builds the full name, piece-by-piece in the FullName variable:

Build name

Concatenate the FullName variable to itself, followed by the value of the cell, followed by a space.

The code will add an unnecessary space to the end of the full name.  We can remove this after we've finished looping through the columns and constructed the full name.  Add an instruction between the Next ColNum and Next RowNum lines to do this:

Trim

The Trim function removes any spaces at the beginning or end of a string. Alternatively, you could use the RTrim function to remove spaces at the right of the string.

Now that we've constructed the full name, we need to write the name into the relevant cell in the worksheet before proceeding to the next row.  We should also clear the contents of the FullName variable ready to build the next name.  Add two more instructions to do this:

Write name

Write the full name into the cell in the fourth column of the current row.

 

Run the subroutine and check the results in Excel:

Result

The fourth column will contain the full name of each character.

Take care to clear the contents of the fourth column before running the subroutine again!

To practise counting through cells

we'll loop over the table of character names and transpose the data in a new worksheet:

  1. Create a new subroutine called TransposeData and declare six variable as shown in the code below:

Sub TransposeData()

 

Dim RowNum As Long

Dim FirstRow As Long

Dim LastRow As Long

 

Dim ColNum As Long

Dim FirstCol As Long

Dim LastCol As Long

 

End Sub

  1. Add code to select Sheet1 and then store the number of the first and last row and column in the appropriate variable (we'll include the column headings in the data this time):

Sub TransposeData()

 

Dim RowNum As Long

Dim FirstRow As Long

Dim LastRow As Long

 

Dim ColNum As Long

Dim FirstCol As Long

Dim LastCol As Long

 

Sheet1.Select

 

FirstRow = 1

LastRow = Range("A1").End(xlDown).Row

 

FirstCol = 1

LastCol = Range("A1").End(xlToRight).Column

 

End Sub

  1. Add an instruction to insert a new worksheet and then add a For Next loop to count through the rows of data on Sheet1:

Sub TransposeData()

 

Dim RowNum As Long

Dim FirstRow As Long

Dim LastRow As Long

 

Dim ColNum As Long

Dim FirstCol As Long

Dim LastCol As Long

 

Sheet1.Select

 

FirstRow = 1

LastRow = Range("A1").End(xlDown).Row

 

FirstCol = 1

LastCol = Range("A1").End(xlToRight).Column

 

Worksheets.Add

 

For RowNum = FirstRow To LastRow

 

Next RowNum

 

End Sub

  1. Add another For Next loop within the first to count through the columns of data:

Sub TransposeData()

 

Dim RowNum As Long

Dim FirstRow As Long

Dim LastRow As Long

 

Dim ColNum As Long

Dim FirstCol As Long

Dim LastCol As Long

 

Sheet1.Select

 

FirstRow = 1

LastRow = Range("A1").End(xlDown).Row

 

FirstCol = 1

LastCol = Range("A1").End(xlToRight).Column

 

Worksheets.Add

 

For RowNum = FirstRow To LastRow

For ColNum = FirstCol To LastCol

 

Next ColNum

Next RowNum

 

End Sub

  1. Add an instruction within the second loop to set the value of a cell using the RowNum and ColNum variables:

For RowNum = FirstRow To LastRow

For ColNum = FirstCol To LastCol

 

ActiveSheet.Cells(ColNum, RowNum).Value = _

Sheet1.Cells(RowNum, ColNum).Value

 

Next ColNum

Next RowNum

  1. Run the subroutine and check the results in Excel:
Result

The new worksheet will contain the same data rearranged by column rather than by row.

 
  1. Save and close the workbook.
This page has 0 threads Add post