Streamlining Excel Code?

[MHadden]

A friend asked me to help him streamline the following code. I tried Select Case, but couldn't get it to work. The code, as it is, works - he was just hoping to shorten it. I am posting only a small amount here, but it follows this same pattern for several cells.

Private Sub PrintDispatchForms_Click()
    Sheets("Program Information").Select
    If (Cells(2, "B") <> "") Then
        DriverName = Cells(2, "B")
        DriverLocation = Cells(2, "A")
        Sheets("Dispatch Driver Log").Select
        Cells(1, "C") = DriverName
        Cells(3, "C") = DriverLocation
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    End If
    Sheets("Program Information").Select
    If (Cells(3, "B") <> "") Then
        DriverName = Cells(3, "B")
        DriverLocation = Cells(3, "A")
        Sheets("Dispatch Driver Log").Select
        Cells(1, "C") = DriverName
        Cells(3, "C") = DriverLocation
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    End If
    Sheets("Program Information").Select
    If (Cells(4, "B") <> "") Then
        DriverName = Cells(4, "B")
        DriverLocation = Cells(4, "A")
        Sheets("Dispatch Driver Log").Select
        Cells(1, "C") = DriverName
        Cells(3, "C") = DriverLocation
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    End If

Any help is appreciated. If you need the full code, I can include it, but it's rather repetitive.

Thanks - in - advance
- Michael

MichaelHadden@yahoo.com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!

 

[cLFlaVA]

try this:

Dim i as Integer

For i = 2 To 4 Step 1
    Sheets("Program Information").Select
    If (Cells(i, "B") <> "") Then
        DriverName = Cells(i, "B")
        DriverLocation = Cells(i, "A")
        Sheets("Dispatch Driver Log").Select
        Cells(i-1, "C") = DriverName
        Cells(i+1, "C") = DriverLocation
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    End If
Next i

If this isn't what you're looking for, please post the complete, functioning code.

Thanks.

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[MHadden]

It worked great!!
Thank you for your help!
- Michael

MichaelHadden@yahoo.com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!

 

[SkipVought]

hi,

Private Sub PrintDispatchForms_Click()
    With Sheets("Program Information")
        For r = 2 To 4
            Sheets("Dispatch Driver Log").Cells(1, "C") = .Cells(r, "B")
            Sheets("Dispatch Driver Log").Cells(3, "C") = .Cells(r, "A")
            Sheets("Dispatch Driver Log").PrintOut Copies:=1, Collate:=True
        Next
    End With
End Sub

faq707-4105 How Can I Make My Code Run Faster?



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244