calling all sheep

[Hannah]

OK...i'm revisiting with this problem....

the below code sorts through a number of rows and when it finds a change in column a it inserts a row. Also, when it finds the end of a working week it inserts another row.

Now the problem is, there is a fixed startdate and the working week is based on increments of 7, but if the first date for each section (the sections are divided based on a change in column a, where the other row is inserted) is greater by seven days than the start date it inserts a line. This is a problem as it isn't always the end of a working week, which is wehre i want the rows entered.

Hope I've explained that little issue well enough....any suggestions and/or subsequent solutions will be met with great gushing praise...

Han...
Sub weeklyTotal()
Const StartDate As Date = #6/29/2002#
Dim TestDate As Date
Dim RowNo As Long
Dim c As Range
TestDate = StartDate
RowNo = Range("A11").Row
Set c = Cells(RowNo, 1)
Do
' If next cell in Column A is blank all done
If IsEmpty(c.Offset(1, 0)) Then
c.Offset(1, 0).EntireRow.Select
Selection.Interior.ColorIndex = 3
Exit Do
End If
' Test for change in code in column A
If c.Value <> c.Offset(1, 0).Value Then
c.Offset(1, 0).EntireRow.Insert shift:=xlUp
c.Offset(1, 0).EntireRow.Select
Selection.Interior.ColorIndex = 3
TestDate = StartDate
RowNo = RowNo + 1
' Test for new week
ElseIf c.Offset(1, 3).Value - TestDate > 7 Then
TestDate = TestDate + (Int((c.Offset(1, 3).Value - TestDate) / 7) * 7)
c.Offset(1, 0).EntireRow.Insert shift:=xlUp
c.Offset(1, 0).EntireRow.Select
Selection.Interior.ColorIndex = 3
RowNo = RowNo + 1
End If
RowNo = RowNo + 1
Set c = Cells(RowNo, 1)
Loop

End Sub

 

[Legacy 11273]

Instead of putting a fixed StartDate in your macro, and then updating the TestDate, suggest you eliminate all code containing StartDate and TestDate.
Then add code instead to check whether the date in column D is the end of a week.
Something like :-

ElseIf Weekday(c.Offset(1, 3)) = 7 Then
Insert, etc.

 

[Hannah]

hi,

thanks for your input, i don't know if it's just me or it's too early in the morning or whatever, but that doesn't make a whole lot of sense to me.....

could someone explain what exactly the jist of this is...

like how would weekday be defined....and isn't the = 7 a little limiting???

as i said though...i may not have a clue...:Þ

 

[Legacy 11273]

My understanding is that you want to insert a row if the date in column D is the last day of a week.

The worksheet formula to return the weekday (i.e. numbers 1 to 7, with Sunday being 1) is :-

=WEEKDAY(A1)

If you require Sunday to be the end of the week instead of Saturday, then :-

=WEEKDAY(A1,2)

Have a look at the WEEKDAY function in the help file.

Therefore, to use this function in your macro to check whether the column D date is the last day of the week :-

Either

ElseIf Weekday(c.Offset(1, 3)) = 7 Then

Or

ElseIf Weekday(c.Offset(1, 3),2) = 7 Then

 

[Hannah]

On 2002-10-15 20:17, Bali wrote:
My understanding is that you want to insert a row if the date in column D is the last day of a week.

The worksheet formula to return the weekday (i.e. numbers 1 to 7, with Sunday being 1) is :-

=WEEKDAY(A1)

If you require Sunday to be the end of the week instead of Saturday, then :-

=WEEKDAY(A1,2)

Have a look at the WEEKDAY function in the help file.

Therefore, to use this function in your macro to check whether the column D date is the last day of the week :-

Either

ElseIf Weekday(c.Offset(1, 3)) = 7 Then

Or

ElseIf Weekday(c.Offset(1, 3),2) = 7 Then

Ok this is good.....but it runs into the same hurdle as with the other one, there isn't always a straight-forward end of the week, sometimes there are only values for mondays or tuesdays or whatever, and these ones get skipped because they don't end on a friday/saturday etc etc

any more suggestions?

 

[Hannah]

....how about a proposition...???

...you all tell me whether this is worth worrying about, or if it's impossible and can't be done and i should move on with my life....

deal???? I just can't stand the waiting if it's all going to be for naught

 

[Hannah]

...ok i'm sorry to be annoying but i'm just DYING to know if anyone has any kind of input at all...

i'm trying to decide whether this is a lost cause (of course i'd rather it wasn't but....)

so any input....even if you say "Han, you've got no chance," would be greatly appreciated!

Ta...

Han

 

[Andrew Poulsom]

Hannah,

Post a sample of your data and I'll have another look. The code I wrote worked with your original data.

 

[lagavulin]

Second attempt to send.....

Reply to "calling all sheep"

Hannah:

I suggest replacing your
If.........ElseIf.........End If code with this:
=======
' Test for change in code in column A
If c.Value <> c.Offset(1, 0).Value Then
c.Offset(1, 0).EntireRow.Insert shift:=xlUp
c.Offset(1, 0).EntireRow.Select
Selection.Interior.ColorIndex = 3
RowNo = RowNo + 1
' Test for new week
ElseIf Int((c.Offset(0, 3).Value - StartDate) / 7) _
<> Int((c.Offset(1, 3).Value - StartDate) / 7) Then
c.Offset(1, 0).EntireRow.Insert shift:=xlUp
c.Offset(1, 0).EntireRow.Select
Selection.Interior.ColorIndex = 3
RowNo = RowNo + 1
End If
=======

If start date is a Sunday, this starts a new weeks on Sunday.

It allows different processes for change code and change week.
If you really want the same process, combine the two conditions with "Or".

 

[Hannah]

you're all being really helpful...but i think i've figured out how to do it....but i'm not exactly sure how to get around it if you know what i mean.....

i'm using a different startdate....one that is gained from the first entry of each division...and this is good but it has a flaw....i need the week to be a working week, and if the first entry is for a wednesday, that's what the startdate becomes and i need the working week so i'd rather the monday of the current week to be the one........

what i thought i could do is use the weekday thing to take it back to the start of that working week.

for example, say the first entry for the section is wed, 28th aug (so 4 using weekday right?), this would become startdate, but what i want it to do is go back to the first monday before that (so monday 26th aug)

so if anyone has any ideas, or thinks this isn't a good idea, then please let me know....you know i'll be grateful

wed 28th aug