INSERTING ROWS WITH A MACRO

[Jay Dean]

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 Phillips]

--

HTH

Bob Phillips

 

[Bob Phillips]

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.

 

[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!

 

[Jay Dean]

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.

 

[David McRitchie]

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
compare:
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

 

[David McRitchie]

Hi Jay,
Don't know if you might have invalid data in your range, but I noticed
that Cecil's will go into a continuous loop if one data point is
outside the expected range. i included a test in mine but hadn't
actually tested that until testing Cecil's his is about 4 lines shorter,
two of mine are color coding.. -- David McRitchie

Jay,
try this code

[..clipped..]

 

[Cecilkumara Fernando]

David,
You are right, can include
***For i = LR To 2 Step -1***
If Cells(i - 1, "A").Value < Cells(1, "A").Value Or _
Cells(i - 1, "A").Value > Cells(1, "A").Value + 6 Then
MsgBox ("the date is out of range at " & Range("A" & i - 1).Address)
Exit Sub
End If
***If Cells(i, "A").Value = Cells(1, "A").Value Then***
to check it
But yours is better.
Thanks and Regards
Cecil

Hi Jay,
Don't know if you might have invalid data in your range, but I noticed
that Cecil's will go into a continuous loop if one data point is
outside the expected range. i included a test in mine but hadn't
actually tested that until testing Cecil's his is about 4 lines shorter,
two of mine are color coding.. -- David McRitchie

Jay,
try this code

[..clipped..]

 

[Jay Dean]

Bob,David,Myrna,Cecil -
I tried Bob's very first code again and it seemed to be
a step closer to what I want. Bob's first code below will insert blank
rows and for any missing dates and update the dates for the first set
of 7 dates, as long as as the dates are not greater than the 7th.

IF Bob's first code can be modified to

(1) insert and correct dates for down the whole column as long as
there are dates (not just the first seven rows in the column) and

(2) work for ALL dates (not just dates up to the 7th)

It will BE EXACTLY what I am looking for. Thanks!

Jay Dean

-------------------------------------------------------------------
Bob's first 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
End Sub
-----------------------------------------------------------------------

David,
You are right, can include
***For i = LR To 2 Step -1***
If Cells(i - 1, "A").Value < Cells(1, "A").Value Or _
Cells(i - 1, "A").Value > Cells(1, "A").Value + 6 Then
MsgBox ("the date is out of range at " & Range("A" & i - 1).Address)
Exit Sub
End If
***If Cells(i, "A").Value = Cells(1, "A").Value Then***
to check it
But yours is better.
Thanks and Regards
Cecil

Hi Jay,
Don't know if you might have invalid data in your range, but I noticed
that Cecil's will go into a continuous loop if one data point is
outside the expected range. i included a test in mine but hadn't
actually tested that until testing Cecil's his is about 4 lines shorter,
two of mine are color coding.. -- David McRitchie

Jay,
try this code

[..clipped..]

 

[David McRitchie]

Hi Jay,
Your directions are too confusing and probably contradictory.

This is what Cecil and I gave you where * is inserted row
that was previously missing. No dates before 2003-09-02
no dates after 2003-09-08 as per our understanding of seven
days, if you started on 2003-09-02

2003-09-02
2003-09-03
2003-09-04 *
2003-09-05 *
2003-09-06 *
2003-09-07 *
2003-09-08 *
2003-09-02 *
2003-09-03
2003-09-04
2003-09-05
2003-09-06 *
2003-09-07
2003-09-08 *

If you don't want duplicate dates as you had in your example and
it has nothing to do with seven then Myrna's example possibly.

Your example is flawed because you do not show or distinguish
what you have and what you want. Repeating the same directions
does not change anything. You must be very clear on what you
want.

Try to phrase things more directly, rather than placing negatives
at the end to change the whole meaning of what was just read.

Again it I ask, what is this for, it really does not seem to have
a practical purpose, except as possibly a homework assignment.

I would suggest that you take a try at modifying the coding that
has been supplied and post your own code, specify whether your
modified code worked or not. If it didn't what it is missing.
If you don't want to do that, at least specify exactly what you have
and what you want in a *short* example that demonstrates exactly
what you want without ambiguity.

Your new instructions are meaningless.

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

 

[Jay Dean]

David -
I thought so too- that maybe I am probably not making myself
clear enough. I apologize for the confusion. ** Practically,I would
use this macro to simplify a job responsibility --- If there was a
way, I would have e-mailed you the exact report. Please, let me
re-explain this in a more practical sense.

** I get a report in an Excel sheet which looks exactly like:

--------------------------------------------------------------
9/10/03
9/11/03 Let's call this set of 7 successive dates "Block 1".
9/12/03
9/13/03 ** Note:
9/14/03 By 7 successive dates, I mean if you count the dates
9/15/03 in this block (enclosed by lines) they are 7 in number.
9/16/03
---------------------------------------------------------------
9/10/03
9/11/03
9/12/03
9/13/03 Let's call this set of 7 dates "Block 2 "
9/14/03
9/15/03
9/16/03
------------------------------------------------------------
9/10/03
9/11/03
9/12/03
9/13/03 This one "Block 3"
9/14/03
9/15/03
9/16/03
------------------------------------------------------------

-- The report could contain any number of these "blocks".

* As you can see, each "Block" has two distinct properties:

** (1) The *number* of dates in each *Block* is 7.

** (2) The set of successive dates in each "Block" are the same dates,
regardless of what dates they may be.

-- I want a macro that will loop through the date column of each
"block" of cells. If the macro finds that in any "block" there are
missing dates then it should insert blank rows and update the dates.

** As an example-- Suppose there is a "Block 4" below "Block 3" in the
above illustration which looks like
------------------------------------------------------------
9/10/03
9/11/03
9/14/03 "Block 4"
9/15/03
9/16/03
------------------------------------------------------------

** We see that "Block 4 " has 5 number of dates instead of 7 number
of dates. Its dates column is missing 9/12/03 and 9/13/03. So, when
the macro loops through "Block 4", it will insert 2 blank rows between
9/11/03 and 9/14/03, then update the block with those two dates. That
way "Block 4" becomes like the below with 7 number of dates
------------------------------------------------------------
9/10/03
9/11/03
9/12/03----inserted row by macro
9/13/03---- inserted row by macro
9/14/03
9/15/03 "Corrected Block 4"
9/16/03
------------------------------------------------------------

**In the same way,the macro would "scan" through the date column of
all existing "Blocks" and for any missing dates, insert equivalent
number of rows and update the dates column.**

-- In the end, every "Block" would contain 7 number of dates. The
dates will still be the same serial number for every "Block".

-- On the other hand, if the macro loops through all the date columns
and finds that every "Block" contains 7 dates, of the same serial
number, then it won't have to do anything.

Please pardon me. I am not too good at making myself clear and I
hope the above illustration clarifies what I am trying to seek help
with. Again, sorry for any confusion and contradiction. Thanks.

Jay Dean

Hi Jay,
Your directions are too confusing and probably contradictory.

This is what Cecil and I gave you where * is inserted row
that was previously missing. No dates before 2003-09-02
no dates after 2003-09-08 as per our understanding of seven
days, if you started on 2003-09-02

2003-09-02
2003-09-03
2003-09-04 *
2003-09-05 *
2003-09-06 *
2003-09-07 *
2003-09-08 *
2003-09-02 *
2003-09-03
2003-09-04
2003-09-05
2003-09-06 *
2003-09-07
2003-09-08 *

If you don't want duplicate dates as you had in your example and
it has nothing to do with seven then Myrna's example possibly.

Your example is flawed because you do not show or distinguish
what you have and what you want. Repeating the same directions
does not change anything. You must be very clear on what you
want.

Try to phrase things more directly, rather than placing negatives
at the end to change the whole meaning of what was just read.

Again it I ask, what is this for, it really does not seem to have
a practical purpose, except as possibly a homework assignment.

I would suggest that you take a try at modifying the coding that
has been supplied and post your own code, specify whether your
modified code worked or not. If it didn't what it is missing.
If you don't want to do that, at least specify exactly what you have
and what you want in a *short* example that demonstrates exactly
what you want without ambiguity.

Your new instructions are meaningless.

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

IF Bob's first code can be modified to

(1) insert and correct dates for down the whole column as long as
there are dates (not just the first seven rows in the column) and

(2) work for ALL dates (not just dates up to the 7th)

It will BE EXACTLY what I am looking for. Thanks!

Jay Dean

 

[Myrna Larson]

I don't understand what you mean by "ALL dates not just dates up to the 7th".

I thought you said you had repeating groups, each of which is supposed to consist of 7
consecutive days. I think everybody assumed that this was the SAME set of 7 days. Are you now
saying that isn't true? That there are perhaps 7 different sets of dates covering a total of 49
days?

If the latter is the case, how is one supposed to know where one (incomplete) set ends and
another starts? Given the dates 8/1 and 8/3, how would you know that, whether both dates are
part of one set, with 8/2 missing, vs one set ending with 8/1 and another set beginning with
8/3, and 8/2 not included in either set?

I think you need to tell us more specifically how you decide that a date is missing.

Bob,David,Myrna,Cecil -
I tried Bob's very first code again and it seemed to be
a step closer to what I want. Bob's first code below will insert blank
rows and for any missing dates and update the dates for the first set
of 7 dates, as long as as the dates are not greater than the 7th.

IF Bob's first code can be modified to

(1) insert and correct dates for down the whole column as long as
there are dates (not just the first seven rows in the column) and

(2) work for ALL dates (not just dates up to the 7th)

It will BE EXACTLY what I am looking for. Thanks!

Jay Dean

-------------------------------------------------------------------
Bob's first 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
End Sub
-----------------------------------------------------------------------









"Cecilkumara Fernando" <[email protected]> wrote in message

David,
You are right, can include
***For i = LR To 2 Step -1***
If Cells(i - 1, "A").Value < Cells(1, "A").Value Or _
Cells(i - 1, "A").Value > Cells(1, "A").Value + 6 Then
MsgBox ("the date is out of range at " & Range("A" & i - 1).Address)
Exit Sub
End If
***If Cells(i, "A").Value = Cells(1, "A").Value Then***
to check it
But yours is better.
Thanks and Regards
Cecil

Hi Jay,
Don't know if you might have invalid data in your range, but I noticed
that Cecil's will go into a continuous loop if one data point is
outside the expected range. i included a test in mine but hadn't
actually tested that until testing Cecil's his is about 4 lines shorter,
two of mine are color coding.. -- David McRitchie

Jay,
try this code
[..clipped..]

 

[Myrna Larson]

Sounds to me like there are two critical questions:

1. How do you know where the block changes? Is there a delimiter between them, as you show in
the message?

2. How do you know what the dates are supposed to be in each block?

Here are some "issues" that have to be dealt with:

If the 1st block included dates 9/10 through 9/15 -- 6 dates -- how would one know whether the
first one or the last one is missing? i.e. is the block supposed to be 9/10 through 9/16, or 9/9
through 9/15?

I considered finding the minimum date and assuming it's the 1st date, and the maximum and
assuming it's the 7th. But it's theoretically possible that the 1st date was missing from ALL
blocks, or the last date is missing from all blocks. e.g. if the earliest date in the column was
9/2 and the latest 9/7, how would you know whether it's 9/1 that's missing or 9/8?

I think you need to have

1. an identifiable block delimiter

2. the dates within a block are sorted (if they aren't, would you expect the code to sort them?)

3. a list of expected dates is somewhere else on the worksheet (or the code could throw up an
input box that asks for the first date)

If those requirements are met, the code is relatively straight-forward: find the next block
delimiter, then the one after it, check that they are 8 rows apart and all seven required dates
are present, in order, in the rows between. Repeat those steps until the end of the data in
column A.

In fact, without the block delimiter and the list of valid dates, there are far too many
assumptions that would have to be made in the code.

 

[David McRitchie]

Hi Jay,
What I gave you was exactly what you are asking for.
Did you actually try it with your data. Are you dates entered
as dates.

 

[Myrna Larson]

Does this do what you are after?

If so, and you have a lot of data, you'll want to turn screen updating off,
set calculation to manual, and disable events at the top of the code, then
reverse those settings at the bottom.

Note that the code assumes there are headers in row 1 and the earliest date
is given in cell K1.


Option Explicit

Sub AddMissingRows()
Dim D As Long
Dim DateList(1 To 7) As Long
Dim i As Long
Dim R As Long

DateList(1) = CLng(Range("K1").Value)
For i = 2 To 7
DateList(i) = DateList(i - 1) + 1
Next i

On Error GoTo BadData
R = Cells(65536, 1).End(xlUp).Row
D = CLng(Cells(R, 1).Value)
Do
For i = 7 To 1 Step -1
If D <> DateList(i) Then
Rows(R + 1).Insert
Cells(R + 1, 1).Value = CDate(DateList(i))
Else
R = R - 1
If R = 1 Then Exit Do
D = CLng(Cells(R, 1).Value)
End If
Next i
Loop
Exit Sub

BadData:
MsgBox "Bad data in A" & R
End Sub

 

[Myrna Larson]

As I said, Jay emailed me about a problem.

I've found a logical error: I was aborting the For/Next loop when I hit the header row. That
meant that rows would never be inserted above row 2, even if A2 doesn't contain the 1st date in
the series, i.e. there are dates missing at the top. (When I tested the code, my first data
block was OK -- my error!)

All blocks must contain 7 dates, so the For/Next loop should never be aborted. The following
code corrects the bug. I also made a change so that if there's no header, only one line of the
code must be changed.

This fix takes care of problems with missing dates at the very top. But Jay said the 1st block
was OK but blocks below not. I can't reproduce that problem.

Option Explicit

Sub AddMissingRows()
Dim D As Long
Dim DateList(1 To 7) As Long
Dim i As Long
Dim R As Long

'change following to 0 if there's no header
Const HeaderRow = 1

DateList(1) = CLng(Range("K1").Value)
For i = 2 To 7
DateList(i) = DateList(i - 1) + 1
Next i

On Error GoTo BadData
R = Cells(65536, 1).End(xlUp).Row
D = CLng(Cells(R, 1).Value)

Do Until R = HeaderRow
For i = 7 To 1 Step -1
If D <> DateList(i) Then
Rows(R + 1).Insert
Cells(R + 1, 1).Value = CDate(DateList(i))
Else
R = R - 1
If R > HeaderRow Then
'not the header: get next date
D = CLng(Cells(R, 1).Value)
Else
'header: set D = invalid date so if i <> 1
'the For/Next loop will complete and insert
'any rows missing at the top
D = DateList(1) - 1
End If
End If
Next i
Loop

Exit Sub

BadData:
MsgBox "Bad data in A" & R
End Sub