I need a macro code for the following.
In Excel, my worksheet contains a colum of dates
grouped in a repetition/successions of 7 dates like

9/1/2003 pen School
9/2/2003 paper .
9/3/2003 book .
9/4/2003 toy .
9/5/2003 drink .
9/6/2003 snack .
9/7/2003 .. .
9/1/2003 shoes Camp
9/2/2003 hat .
9/3/2003 ............the group of repeated
7 dates continues down the column. I would like a code
for a macro that will check the date column for as
far down the column as possible to make sure that for
EACH block of 7 days no number of date rows are
missing.

For Example: The macro would check that from
9/3/2003 the next row down contains 9/4/2003, not
9/7/2003
If any number of dates are missing the macro should
insert blank rows and update the date column for the
inserted blank rows--that's it!

That is, in the Example above if 9/7/2003 is the next
date under 9/3/2003, then the macro should insert 3
blank rows next below 9/3/2003 and update them as
9/4/2003
9/5/2003
9/6/2003,
then the 9/7/2003.

Please help. Thanks!

Jay Dean

--

HTH

Bob Phillips

"Jay Dean" wrote in message
om...
I need a macro code for the following.
In Excel, my worksheet contains a colum of dates
grouped in a repetition/successions of 7 dates like

9/1/2003 pen School
9/2/2003 paper .
9/3/2003 book .
9/4/2003 toy .
9/5/2003 drink .
9/6/2003 snack .
9/7/2003 .. .
9/1/2003 shoes Camp
9/2/2003 hat .
9/3/2003 ............the group of repeated
7 dates continues down the column. I would like a code
for a macro that will check the date column for as
far down the column as possible to make sure that for
EACH block of 7 days no number of date rows are
missing.

For Example: The macro would check that from
9/3/2003 the next row down contains 9/4/2003, not
9/7/2003
If any number of dates are missing the macro should
insert blank rows and update the date column for the
inserted blank rows--that's it!

That is, in the Example above if 9/7/2003 is the next
date under 9/3/2003, then the macro should insert 3
blank rows next below 9/3/2003 and update them as
9/4/2003
9/5/2003
9/6/2003,
then the 9/7/2003.

Please help. Thanks!

Jay Dean

Jay,

Here's some code
Dim cRows As Long, i As Long

For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Day(Cells(i, "A").Value) 1 Then
If Cells(i, "A").Value < Cells(i - 1, "A").Value + 1 Then
Cells(i, "A").EntireRow.Insert
Cells(i, "A").Value = Cells(i + 1, "A").Value - 1
i = i + 1
End If
End If
Next i

You don't say what to say with dates greater than 7th, so I have ignored
that.

--

HTH

Bob Phillips

"Jay Dean" wrote in message
om...
I need a macro code for the following.
In Excel, my worksheet contains a colum of dates
grouped in a repetition/successions of 7 dates like

9/1/2003 pen School
9/2/2003 paper .
9/3/2003 book .
9/4/2003 toy .
9/5/2003 drink .
9/6/2003 snack .
9/7/2003 .. .
9/1/2003 shoes Camp
9/2/2003 hat .
9/3/2003 ............the group of repeated
7 dates continues down the column. I would like a code
for a macro that will check the date column for as
far down the column as possible to make sure that for
EACH block of 7 days no number of date rows are
missing.

For Example: The macro would check that from
9/3/2003 the next row down contains 9/4/2003, not
9/7/2003
If any number of dates are missing the macro should
insert blank rows and update the date column for the
inserted blank rows--that's it!

That is, in the Example above if 9/7/2003 is the next
date under 9/3/2003, then the macro should insert 3
blank rows next below 9/3/2003 and update them as
9/4/2003
9/5/2003
9/6/2003,
then the 9/7/2003.

Please help. Thanks!

Jay Dean

Bob --
I meant that the column of dates may be ANY successive dates grouped
in sevens. Example

7/10/03
7/11/03
7/12/03
7/13/03
7/14/03
7/15/03
7/16/03
7/17/03 Then the dates repeat again like
7/10/03
7/11/03
7/12/03
7/13/03
7/14/03
7/15/03
7/16/03
7/17/03 then the dates repeat again down the coulmn for as long as
possible.

I need the code for a macro that will loop through the dates column and
ensure that for any group of repeated 7 dates no date is missing. If any
dates are missing in any block of seevn dates then the macro should
insert the equivalent number of rows and update the dates. E.g If it
finds that the date below say 7/11/03 for any group of seven days is
7/15/03, then it should insert 3 blank rows and update the dates column
so 7/11/03 is followed by 7/12/03, 7/13/03, 7/14/03 before the 7/15/03.
Thanks.

Jay



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Jay,
Assuming that there is only one set of 7 recurring dates,
beginning in A1. Modifying Bob's code beyond any
recognition, had planned to use two do loops but
it was not possible for me to think that way.

Would you mind telling us what practical use this is.

sub JayDean()
Dim cRows As Long, i As Long
Dim jMin As Long, jMax As Long, jCheck As Long
jMin = Cells(1, 1).Value
jMax = jMin + 6
jCheck = jMax
i = Cells(Rows.Count, "A").End(xlUp).Row
range("A:A").Font.ColorIndex = xlColorIndexAutomatic
compa
If jCheck < jMin Then jCheck = jMax
If jMin Cells(i, "A") Then GoTo abort
If jMax < Cells(i, "A") Then GoTo abort
If jCheck = Cells(i, "A") Then
i = i - 1
If i = 1 Then GoTo done
jCheck = jCheck - 1
GoTo compare
End If
If jCheck < Cells(i, "A") Then
Cells(i + 1, "A").EntireRow.Insert
Cells(i + 1, "A").Font.ColorIndex = 3
Cells(i + 1, "A").Value = jCheck
jCheck = jCheck - 1
GoTo compare
End If
abort:
MsgBox "Problem at row " & i & ", min=" & jMin & ", " & jMax & ", " & jCheck
done:
End Sub

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"jay dean" wrote in message ...

Bob --
I meant that the column of dates may be ANY successive dates grouped
in sevens. Example

7/10/03
7/11/03
7/12/03
7/13/03
7/14/03
7/15/03
7/16/03
7/17/03 Then the dates repeat again like
7/10/03
7/11/03
7/12/03
7/13/03
7/14/03
7/15/03
7/16/03
7/17/03 then the dates repeat again down the coulmn for as long as
possible.

I need the code for a macro that will loop through the dates column and
ensure that for any group of repeated 7 dates no date is missing. If any
dates are missing in any block of seevn dates then the macro should
insert the equivalent number of rows and update the dates. E.g If it
finds that the date below say 7/11/03 for any group of seven days is
7/15/03, then it should insert 3 blank rows and update the dates column
so 7/11/03 is followed by 7/12/03, 7/13/03, 7/14/03 before the 7/15/03.
Thanks.

Jay

Bob --
The code below does not seem to work. Maybe this will clarify the
code I need.If I have multiple successive seven dates like

9/15/2003
9/16/2003
9/17/2003
9/18/2003
9/19/2003
9/20/2003
9/21/2003
9/15/2003
9/16/2003
9/17/2003
9/18/2003
9/19/2003
9/20/2003
9/21/2003
9/15/2003
9/16/2003
9/17/2003
9/18/2003
9/19/2003
9/20/2003
9/21/2003

It goes way down the column. I need a Macro to loop through the entire
column. If it finds that any dates are missing, it should insert blank
rows for the missing dates.


"Bob Phillips" wrote in message ...
Jay,

Here's some code
Dim cRows As Long, i As Long

For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Day(Cells(i, "A").Value) 1 Then
If Cells(i, "A").Value < Cells(i - 1, "A").Value + 1 Then
Cells(i, "A").EntireRow.Insert
Cells(i, "A").Value = Cells(i + 1, "A").Value - 1
i = i + 1
End If
End If
Next i

You don't say what to say with dates greater than 7th, so I have ignored
that.

--

HTH

Bob Phillips

"Jay Dean" wrote in message
om...
I need a macro code for the following.
In Excel, my worksheet contains a colum of dates
grouped in a repetition/successions of 7 dates like

9/1/2003 pen School
9/2/2003 paper .
9/3/2003 book .
9/4/2003 toy .
9/5/2003 drink .
9/6/2003 snack .
9/7/2003 .. .
9/1/2003 shoes Camp
9/2/2003 hat .
9/3/2003 ............the group of repeated
7 dates continues down the column. I would like a code
for a macro that will check the date column for as
far down the column as possible to make sure that for
EACH block of 7 days no number of date rows are
missing.

For Example: The macro would check that from
9/3/2003 the next row down contains 9/4/2003, not
9/7/2003
If any number of dates are missing the macro should
insert blank rows and update the date column for the
inserted blank rows--that's it!

That is, in the Example above if 9/7/2003 is the next
date under 9/3/2003, then the macro should insert 3
blank rows next below 9/3/2003 and update them as
9/4/2003
9/5/2003
9/6/2003,
then the 9/7/2003.

Please help. Thanks!

Jay Dean