Key Words:

Parallel Project download -- For Each looping -- nested loops -- SpecialCells method

Create Trip Legs

Parallel Project Download

Using the following link, you may download an Excel workbook that is complete up to this point in the project. You do not need to download this workbook if your personal project is completed correctly up to this point.

Download Trip_Planner_Dload02.xls

A previous Excel workbook download was provided at the beginning of the Visual Basic Editor Setup lesson.

Write Code that Creates Trip Legs

In a previous lesson (see Preparing a Worksheet for Data) we created a Lists worksheet for holding data pertaining to our Trip Planner project. The Lists worksheet is set up to provide costs for various legs of the trip depending on what mode of transportation is selected. A trip leg consists of travel from one destination to another destination. Modes of transportation include air, car rental, and bus.

In this lesson, we write VBA that automatically creates the trip legs on our Lists sheet.

More on For Each looping can be found in the main course (see Loops: For, For Each, Do Loops). The SpecialCells method is also discussed in the main course (see Error Handling).

Open your saved workbook or your Parallel Project download 2.
Go to the Lists sheet and view it. Note that there are Destinations filled in, but no Trip Legs and no costs.
Open the VB Editor.
Double-click on the m3Data module in the Project pane
Type the sub shown show below. If you have trouble or you do not want to type it, you can copy the code from the text area at the bottom of the page.
When you are done, put your cursor anywhere within the sub, and press F5 to run the sub.
Go to the Lists sheet and verify that the trip legs were created.

Sub CreateAllLegs()
    Dim sP1, sP2
    Dim gPlug
    Dim iCol
    Dim ocell, ooo
    
    owLists.Activate
    iCol = Range("_Legs").Column
    gPlug = 2
    
    Columns(iCol).Cells.ClearContents
    Cells(1, iCol).Value = "Legs"
    For Each ocell In Columns(1).SpecialCells(xlCellTypeConstants)
        If ocell.Row > 1 Then
            sP1 = ocell.Value
            For Each ooo In Columns(1).SpecialCells(xlCellTypeConstants)
                If ooo.Row > 1 And ooo.Value <> sP1 Then
                    Cells(gPlug, iCol).Value = sP1 & " to " & ooo.Value
                    gPlug = gPlug + 1
                End If
            Next ooo
        End If
    Next ocell
End Sub

VBATrain Home | Samples TOC | Previous