Inserting Dates
 

I have a sheet with data in up to 80 odd columns.
Column A is where the date is stored. My data comes in a 14 day period, however if there is no data entered on the date, then that date does not appear. I need to have all of the dates in the period show up regardless of if they have data or not.

So I want to run a macro to see if there is a date missing, if there is insert a row(s) with that date on it.

That's all. I'm sure that it is simple, unfortunately I am very unsure of how to go about doing this.

Thanks for any help you can offer.

You're probably describing soemthing very simple but unfortunately are doing
it in a very complicated way. Try describing it in sentences.

Mike

"shhhhh" wrote:

Hi I am lookinf for some help with coding this.

if cell a1 is a date then
if cell a2 is a date then
if a2-a1 = 1 then
rw=rw+1
else
insert (a2-a1) rows
rowadd=1
for each row inserted do
(a2-a1)rows = a1+rowadd
rowadd=rowadd+1
loop
rw=rw+1
endif
else
rw=rw+1
endif
rw=rw+1
endif

anybody have any thoughts on how to help me do this?
Thanks.


--------------= Posted using GrabIt =----------------
------= Binary Usenet downloading made easy =---------
-= Get GrabIt for free from http://www.shemes.com/ =-

Inserting Dates
 

Hi,

Try this.

Option Explicit

Sub rowinsert()

Dim lrow As Long, lrowfirst As Long
lrowfirst = 2 ' first row to check
lrow = ActiveSheet.UsedRange.Row + _
ActiveSheet.UsedRange.Rows.Count - 1
Do While lrow lrowfirst ' work backwards
' check if dates
If IsDate(Cells(lrow, 1)) And _
IsDate(Cells(lrow - 1, 1)) Then
' check if running sequence
If Cells(lrow, 1) - 1 < Cells(lrow - 1, 1) Then
' insert the row and date
Rows(lrow).Insert
Cells(lrow, 1) = Cells(lrow + 1, 1) - 1
' increase counter to check row again
' if changed
lrow = lrow + 1
End If
End If
' reduce the counter
lrow = lrow - 1
Loop

End Sub


--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"shhhhh" wrote:

I have a sheet with data in up to 80 odd columns.
Column A is where the date is stored. My data comes in a 14 day period, however if there is no data entered on the date, then that date does not appear. I need to have all of the dates in the period show up regardless of if they have data or not.

So I want to run a macro to see if there is a date missing, if there is insert a row(s) with that date on it.

That's all. I'm sure that it is simple, unfortunately I am very unsure of how to go about doing this.

Thanks for any help you can offer.

You're probably describing soemthing very simple but unfortunately are doing
it in a very complicated way. Try describing it in sentences.

Mike

"shhhhh" wrote:

Hi I am lookinf for some help with coding this.

if cell a1 is a date then
if cell a2 is a date then
if a2-a1 = 1 then
rw=rw+1
else
insert (a2-a1) rows
rowadd=1
for each row inserted do
(a2-a1)rows = a1+rowadd
rowadd=rowadd+1
loop
rw=rw+1
endif
else
rw=rw+1
endif
rw=rw+1
endif

anybody have any thoughts on how to help me do this?
Thanks.


--------------= Posted using GrabIt =----------------
------= Binary Usenet downloading made easy =---------
-= Get GrabIt for free from http://www.shemes.com/ =-

Inserting Dates
 

Assuming the dates start in A1 as in your example:

Sub AddDates()
Dim i As Long
i = 1
Do While Cells(i + 1, 1) < ""
If Int(Cells(i + 1, 1)) Int(Cells(i, 1)) + 1 Then
Rows(i + 1).EntireRow.Insert
Cells(i + 1, 1) = Int(Cells(i, 1)) + 1
End If
i = i + 1
Loop
End Sub

--
Regards,
Tom Ogilvy

"shhhhh" wrote in message
...
I have a sheet with data in up to 80 odd columns.
Column A is where the date is stored. My data comes in a 14 day period,
however if there is no data entered on the date, then that date does not
appear. I need to have all of the dates in the period show up regardless
of if they have data or not.

So I want to run a macro to see if there is a date missing, if there is
insert a row(s) with that date on it.

That's all. I'm sure that it is simple, unfortunately I am very unsure of
how to go about doing this.

Thanks for any help you can offer.

You're probably describing soemthing very simple but unfortunately are
doing
it in a very complicated way. Try describing it in sentences.

Mike

"shhhhh" wrote:

Hi I am lookinf for some help with coding this.

if cell a1 is a date then
if cell a2 is a date then
if a2-a1 = 1 then
rw=rw+1
else
insert (a2-a1) rows
rowadd=1
for each row inserted do
(a2-a1)rows = a1+rowadd
rowadd=rowadd+1
loop
rw=rw+1
endif
else
rw=rw+1
endif
rw=rw+1
endif

anybody have any thoughts on how to help me do this?
Thanks.


--------------= Posted using GrabIt =----------------
------= Binary Usenet downloading made easy =---------
-= Get GrabIt for free from http://www.shemes.com/ =-