Add Columns to Code

Hello,

I would like to add columns 8 and 9 to the below code so that column 7 is not included. The line is ' For ApptCol = 4 To 6'

Can this be done?

The reason I want to do this is so the duration on the Schedule sheet is calculated from the start time to end time.

Sub Appt_Load()
With Schedule
    If .Range("B9").Value = Empty Then
        MsgBox "Please select a correct appointment"
        Exit Sub
    End If
    ApptRow = .Range("B9").Value
    .Range("E3").Value = Appts.Range("C" & ApptRow).Value 'Add in Contact Name
    For ApptCol = 4 To 6
        .Range("E" & ApptCol + 1).Value = Appts.Cells(ApptRow, ApptCol).Value 'Add in Appt Details
    Next ApptCol
End With
Schedule.Range("E3").Select
End Sub

Try:

Sub Appt_Load()
With Schedule
    If .Range("B9").Value = Empty Then
        MsgBox "Please select a correct appointment"
        Exit Sub
    End If
    ApptRow = .Range("B9").Value
    .Range("E3").Value = Appts.Range("C" & ApptRow).Value 'Add in Contact Name
    For ApptCol = 4 To 9
        If ApptCol <> 7 Then
            .Range("E" & ApptCol + 1).Value = Appts.Cells(ApptRow, ApptCol).Value 'Add in Appt Details
        End If
    Next ApptCol
End With
Schedule.Range("E3").Select
End Sub

I don't love the low elegance of this solution but it will work.

Sub Appt_Load()
With Schedule
    If .Range("B9").Value = Empty Then
        MsgBox "Please select a correct appointment"
        Exit Sub
    End If
    ApptRow = .Range("B9").Value
    .Range("E3").Value = Appts.Range("C" & ApptRow).Value 'Add in Contact Name
    For ApptCol = 4 To 9
        If ApptCol <> 7 Then
           .Range("E" & ApptCol + 1).Value = Appts.Cells(ApptRow, ApptCol).Value 'Add in Appt Details
        End If
    Next ApptCol
End With
Schedule.Range("E3").Select
End Sub

@6SJ: hahhh, you copy my code, colour it in, and then call it inelegant!

Originally Posted by TMS

@6SJ: hahhh, you copy my code, colour it in, and then call it inelegant!

Let's just say great minds think alike! I was just slow on the draw.

I just don't like having a loop with exceptions. Some languages would allow this

Formula:

For ApptCol = 4, 5, 6, 8, 9

but not VBA.

I guess you could do this. But I can't think of a good way to express "4-9 except 7" without writing the numbers individually.

Sub Appt_Load()

Dim ApptCol As Variant

With Schedule
    If .Range("B9").Value = Empty Then
        MsgBox "Please select a correct appointment"
        Exit Sub
    End If
    ApptRow = .Range("B9").Value
    .Range("E3").Value = Appts.Range("C" & ApptRow).Value 'Add in Contact Name
    For Each ApptCol In Array(4, 5, 6, 8, 9)
           .Range("E" & ApptCol + 1).Value = Appts.Cells(ApptRow, ApptCol).Value 'Add in Appt Details
    Next ApptCol
End With
Schedule.Range("E3").Select
End Sub

You could have two loops: 4 to 6 and 8 to 9. Not elegant at all. But it would work.

You're welcome. Thanks for the rep.

I quite like the array approach and, at first glance, it looks scalable. Until the OP say, "I’ve got 100 columns and I need to ignore 7 of them … 7, 23, 31, 40, 52, 67 and 82" or whatever .