looping issues

Greetings:

I知 working on a macro to generate work schedules for a boat yard, and I could sure use some expert opinions. I知 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痴 prettier than it was yesterday at this time. I have also attached the customer file it痴 running against. So, with all that said, what I知 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知 working through it equally as efficient?

Thanks in advance - Marcus

HTML Code: 

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,

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

includes the intermediate cells.

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.

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: