Post a Comment On: CPA at Law

This is the third post in a series explaining how I use Visual Basic in Microsoft Excel to increase productivity. In my previous post, I discussed the If Then statement as well as different ways to reference cells on a sheet; in this post, I will discuss the Do Loop.

The Do Loop allows you to repeat the same block of code until a condition is satisfied. To demonstrate, I will be using the client entity table from this earlier post; this Do Loop will concatenate a list of all a client's entities into a single field.

After recreating the client entity table in your own spreadsheet, select blank cell G2.  The code below will list all the entities in the active cell, starting with the entity name in the same row as the active cell, and ending with the last entity name before the client's name changes.

Before moving further, however, it will be important to declare a variable that can be used to keep track of which of the client's entities will appear next on the list. A good practice when declaring any variable is to use Option Explicit (for an explanation why, see this article). Declaring a variable is accomplished by typing "Dim" followed by the name for the variable you select, followed by the data type, which can be an "Integer" in this case (other data types are capable of storing different sizes or kinds of data). After declaring the existence of the variable "r," we assign it a value of 0 for reasons described below. After doing all of this, I have the following as the starting point for my code:

Option Explicit
Sub concatenateEntities()

    Dim r As Integer
    r = 0

End Sub

With "r" set to zero, we will follow with our Do Loop. The basic Do Loop to accomplish the procedure described above is as follows:

    Do Until ActiveCell.Offset(0, -6) <> ActiveCell.Offset(r, -6)

        ActiveCell.Value = ActiveCell.Value & "; " & ActiveCell.Offset(r, -5)
        r = r + 1

    Loop

By running the macro with cell G2 selected on a spreadsheet containing my entity table, the active cell should now read as follows: ";  ABC Corporation;  Doe Family Partnership;  Doe Rental Property, LLC;  Doe Equipment, LLC". Here is how this was accomplished:

The "Do Until" line tells the computer to repeat the code that follows until it says "Loop," repeating until the client name changes. It starts by comparing the value in column A in the same row as the active cell with the value in column A in each subsequent row, one at a time. The "r = r + 1" line keeps increasing the count of the row after the prior line sets the active cell equal to whatever it's current value, followed by a semicolon, followed by the value of the cell in column B that contains the next entity name on the list.

Notice that we probably don't want to have the first semicolon that appears in the active cell, which occurs due to the fact that the first time the code sets the active cell equal to itself before adding the semicolon, the active cell has nothing in it. See if you can use an If Then statement to fix that problem; I will post my solution in the comments that follow. For a tutorial on recording a macro, please stay tuned for my next installment of Basic Visual Basic for Excel.