Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Verify date by week.
Okay I import a txt file that has data by week, but if the week has no
entries it's not there so I need to add that week to the file so I can copy it over into another worksheet. I can't seem to get the right combination of syntax/formula correct. There should always be data in 12/29/07 row. This is what I started out with. For a = 53 to 2 step -1 Range("A"+Cstr(a)+"").activate DateCurr = activecell.value DatePrev = activecell.offset(-1,0).value If DateCurr < DatePrev + 7 then selection.entirerow.insert Range("A"+Cstr(a)+"").value = DateCurr + 7 Next a Do I have to do something with date() function to make this work or do I force column a format to date? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Verify date by week.
Try this:
I'd do it with a copy of your workbook to ensure it gives you what you want. Sub Macro1() Dim myRange As Range For a = 3 To 54 Set myRange = Range("A" & a) If myRange.Value < myRange.Offset(-1, 0).Value + 7 Then Debug.Print myRange.Address, myRange.Value, myRange.Offset(-1, 0).Value Debug.Print myRange.EntireRow.Insert Debug.Print myRange.Address myRange.Offset(-1, 0).Value = myRange.Offset(-2, 0) + 7 End If Next a End Sub "Rominall" wrote: Okay I import a txt file that has data by week, but if the week has no entries it's not there so I need to add that week to the file so I can copy it over into another worksheet. I can't seem to get the right combination of syntax/formula correct. There should always be data in 12/29/07 row. This is what I started out with. For a = 53 to 2 step -1 Range("A"+Cstr(a)+"").activate DateCurr = activecell.value DatePrev = activecell.offset(-1,0).value If DateCurr < DatePrev + 7 then selection.entirerow.insert Range("A"+Cstr(a)+"").value = DateCurr + 7 Next a Do I have to do something with date() function to make this work or do I force column a format to date? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert regular Date to Week Ending or Week Beginning Dates | Excel Discussion (Misc queries) | |||
Check if Date within this week/last week | Excel Programming | |||
Verify user input box is a Month End Date | Excel Programming | |||
Forms - Verify date format | Excel Programming | |||
I need week number in excell from a date, first week must be mini. | Excel Discussion (Misc queries) |