looping issues

**Marcus Gee** · 10-09-2007, 01:37 PM

Greetings:

I’m working on a macro to generate work schedules for a boat yard, and I could sure use some expert opinions. I’m taking an exported customer file from QB, running multiple, conditional delete subs, and now I need to have it calculate the next scheduled service date based on schedule type (col. G), last service date (col. M), and next scheduled service date (col. N), and enter the calculated service date to column T.

Note - The next scheduled service date (column N) is an entry that clerks make when a customer requests service outside of the normal cycle. If found and the date in col. N is >= to Today, it should override the calculated value.

Logically, the DateCalc sub should:
(1) Establish the range of the loop (G2 to last non-blank row)
(2) for each cell in the selected range, and based on the value in col. G (CType = Schedule Type (SchedType)), the value in col. M (CUSTFLD6 = Last Service Date (LastSvcDate)), calculate the next scheduled service date (NextSchedSvc);
(3) compare the value in Calculated Service Date (NextSchedSvc) to the value in col. N (CUSTFLD7 = Next Scheduled Service (NextSvcDate));
(4) if col. N is not blank and the value >= to today, then the Calculated Scheduled Service Date (NextSchedSvc) = Column N value, else the Calculated Next Scheduled Service Date (NextSchedSvc) should remain as calculated, and
(5) enter the calculated value to column T of that row

Believe it or not this is my introduction to VBA Programming. Nothing like jumping into the deep end of the pool!

I have included the entire macro code base to keep the overall structure in context. I realize that the DateCalc sub needs a boatload of work yet, but it’s prettier than it was yesterday at this time. I have also attached the customer file it’s running against. So, with all that said, what I’m struggling with at this point is (1) whether I need to define the range for other columns in addition to column G, or is it sufficient to establish a range for column G, and (2) am I on the right track in terms of my approach. For example, would I be better off doing this with a Select Case or ElseIf structure, or is the way I’m working through it equally as efficient?

Thanks in advance - Marcus

HTML Code:

**mikerickson** · 10-09-2007, 03:12 PM

I haven't read through your code, but I can answer your first question.

if myColG is a range variable of cells in Column G,

myColG.Offset(0,2)

is the same cells 2 columns to the right.
While,

Range(myColG, myColG.Offset(0,2))

includes the intermediate cells.

**Marcus Gee** · 10-09-2007, 05:02 PM

Thanks Mikerikson - I recall reading in one of the moderator's postings that setting ranges in general is a bad idea. Given that, and taking your reply into account, I'm beginning to think a better approach would be to use a Do Until IsEmpty routine together with the offset you mentioned to specify which cells to use in calculating the next service date.

Is that a better approach? Any advice you can offer is appreciated. Thanks again - Marcus

**mikerickson** · 10-09-2007, 06:19 PM

I'm not sure what you mean by "setting ranges in general is a bad idea".

Using range variables is a common way to avoid Selecting ranges. It is faster and all around better than Selecting.

**shg** · 10-09-2007, 08:11 PM

Marcus, try this.

BTW, I've seen lots of mikerickson's posts, and commend his advice.

Sub DateCalc()
    '   (1) Establish the range of the loop (G2 to last non-blank row)
    '   (2) based on value in column G (Label = CType = Schedule Type or _
        '       sSchedType) and date in column M (Label = CUSTFLD6 = Last _
        '       Service Date or datNextSvc), calculate the next scheduled _
        '       service date;
    '   (3) compare value in Calculated Service Date to value in Column N _
        '       (Label = CUSTFLD7 = Next Scheduled Service or datNextSvc;
    '   (4) if Column N is not blank and the value >= to today, Scheduled _
        '       Service Date = Column N value, else Next Scheduled Service _
        '       Date = Calculated Next Scheduled Service Date,
    '   (5) enter the calculated value to column T of that row

    Dim datLastSvc As Date
    Dim datNextSvc As Date
    Dim rngCell As Range
    Dim iMon    As Integer

    iMon = Month(Date)

    For Each rngCell In Range("G2", Range("G65536").End(xlUp))
        datLastSvc = Cells(rngCell.Row, "M").Value

        If IsDate(Cells(rngCell.Row, "N").Value) Then
            datNextSvc = Cells(rngCell.Row, "N").Value
        Else
            datNextSvc = DateValue("1/1/1900")
        End If

        If datNextSvc <= Date Then
            Select Case rngCell.Value
                Case "W"
                    datNextSvc = datLastSvc + 7

                Case "EOW"
                    datNextSvc = datLastSvc + 14

                Case "EOWTh"        ' every other week on Thursday
                    datNextSvc = datLastSvc + 14
                    ' adjust to Thursday
                    datNextSvc = datNextSvc + 5 - Weekday(datNextSvc)

                Case "2xMo"
                    datNextSvc = datLastSvc + 15

                Case "EOWS1xMoW"
                    If iMon >= 5 And iMon <= 10 Then
                        datNextSvc = datLastSvc + 14
                    Else
                        datNextSvc = datLastSvc + 30
                    End If

                Case "ETW"        ' every third week
                    datNextSvc = datLastSvc + 21

                Case "15xYr"
                    If iMon >= 5 And iMon <= 10 Then
                        datNextSvc = datLastSvc + 21
                    Else
                        datNextSvc = datLastSvc + 30
                    End If

                Case "1xMo"
                    datNextSvc = datLastSvc + 30

                Case "EOM"
                    datNextSvc = datLastSvc + 60

                Case "OnCall"
                    ' dunno ...

                Case "Q"
                    datNextSvc = datLastSvc + 90

                Case Else
                    rngCell.Select
                    MsgBox "Schedule type??", vbCritical
                    Exit Sub

            End Select
        End If
    Next rngCell

    Cells(rngCell.Row, "T") = datNextSvc
End Sub

**Marcus Gee** · 10-09-2007, 09:58 PM

Mikerickson and SHG - Thanks a million - I took your code and plugged it in, then went through it to add some comments (to ensure I knew what it was doing). I made one change that worked (moving the statement that posts the datNextSvc to the active row, column T to just above the End If Statement).

I made one other change that didn't work, that being date calc for "OnCall" services. For some reason the code I entered returns a value of 12:00:00 PM.

In the process of running the code I also discovered that one of the admin people had entered a Schedule Type of Contract for some reason, which crashes the sub. I'll need to add some code at some point to identify entries other than the designated schedule types and put it on an error report, but other than that, it works. I've included the (slightly) modified code below.

Again, thank you. Y'all made my week!

Marcus

HTML Code: