Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ =- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ =- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ =- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting Dates | Excel Programming | |||
Inserting Dates | Excel Discussion (Misc queries) | |||
Inserting dates | Excel Discussion (Misc queries) | |||
Inserting Dates | Excel Discussion (Misc queries) | |||
Inserting dates in a worksheet | Excel Programming |