Excel Visual Basic for Applications online training

This page has 0 threads | Add post

7,326 reviews

Self-paced training

Finding our tutorials useful? Please consider helping us to share more!

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:

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:

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:

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 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:

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 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:

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:

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:

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 to the FirstCol and LastCol variables as shown here.

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

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:

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:

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 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:

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:

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 the full name into the cell in the fourth column of the current row.

Run the subroutine and check the results in Excel:

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:

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

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

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

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

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

Run the subroutine and check the results in Excel:

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

Save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

The Example Workbook

Counting Through Rows

Counting Through Columns

Building a Full Name

Head office

London

Manchester