Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
INSERTING ROWS WITH A MACRO
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
INSERTING ROWS WITH A MACRO
-- 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
INSERTING ROWS WITH A MACRO
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
INSERTING ROWS WITH A MACRO
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
INSERTING ROWS WITH A MACRO
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
INSERTING ROWS WITH A MACRO
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
INSERTING ROWS WITH A MACRO
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 "Cecilkumara Fernando" wrote ... Jay, try this code [..clipped..] |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
INSERTING ROWS WITH A MACRO
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 "David McRitchie" wrote in message ... 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 "Cecilkumara Fernando" wrote ... Jay, try this code [..clipped..] |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
INSERTING ROWS WITH A MACRO
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" 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 "David McRitchie" wrote in message ... 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 "Cecilkumara Fernando" wrote ... Jay, try this code [..clipped..] |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
INSERTING ROWS WITH A MACRO
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" wrote in message om... 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
INSERTING ROWS WITH A MACRO
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 "David McRitchie" wrote in message ... 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" wrote in message om... 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
INSERTING ROWS WITH A MACRO
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. -------------------------------------------------------------- 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
INSERTING ROWS WITH A MACRO
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. --- 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 om... ** (2) The set of successive dates in each "Block" are the same dates, regardless of what dates they may be. |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
INSERTING ROWS WITH A MACRO
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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
INSERTING ROWS WITH A MACRO
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro auto inserting rows and formulas | Excel Discussion (Misc queries) | |||
I need a macro for inserting a border line every 20 rows | Excel Discussion (Misc queries) | |||
Macro Help- Inserting Blank Rows | Excel Discussion (Misc queries) | |||
Macro needed for inserting blank rows | Excel Worksheet Functions | |||
Inserting Blank Rows Macro? | Excel Worksheet Functions |