My ref: RN0001 Find the first & last occurrence of a strings

[robzrob]

I'm setting up a staff record sheet that each member can use to record
their hours. I've set up a grid of a week with columns thus: week
number, day, date, times in and out, total day's hours, flexi balance,
etc. The rows descend in days. I want to be able to write a
procedure called 'Add A New Week' so that when the current week-grid
is full and finished with, the user can put in another week-grid below
it. I thought I would do it by copying and pasting the original week
grid under the last row of the last week. How do I find the first
occurence of MON (Monday) so that I can make the procedure select the
right range of cells to copy and then how do I find the last
occurrence of SUN (Sunday) so I can make the procedure paste the new
grid under the last Sunday row?

 

[Rick Rothstein]

I presume the Week Number, Day and Date get pre-filled in for the user; exactly what is in those cells... formulas or text constants?

 

[robzrob]

I presume the Week Number, Day and Date get pre-filled in for the user; exactly what is in those cells... formulas or text constants?

--
Rick (MVP - Excel)





- Show quoted text -

Some text, some formulas, but I'm not really interested in pasting
what's in them, just setting up the new week grid first - in the
right place (so I would just be pasting the format of the grid
cells.) I thought I'd put in the cell contents (which will be related
to the previous week's values, eg week number, date, flexi balance) in
later steps of the procedure. Probably a long way round, but I'm a
beginner and thought that this would be a good exercise. Is this a
bad idea?

 

[Rick Rothstein]

Assuming that the Week Number is text and the Day and Date are formulas (basically, =Bn+1 and =Cn+1 where n is the preceding row number produced by copying down), this macro should do what you want (assuming I understand your layout correctly)...

Sub AddNextWeek()
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(LastRow, "A").EntireRow.Resize(8, 3).FillDown
Cells(LastRow + 1, "A").Resize(7).Value = Cells(LastRow, "A").Value + 1
End Sub

If this doesn't do what you want, then you will have to tell us, in detail, how your worksheet is laid out, what is in the various cells and what parts of them you want placed for the next week.

--
Rick (MVP - Excel)

I presume the Week Number, Day and Date get pre-filled in for the user; exactly what is in those cells... formulas or text constants?

--
Rick (MVP - Excel)





- Show quoted text -

Some text, some formulas, but I'm not really interested in pasting
what's in them, just setting up the new week grid first - in the
right place (so I would just be pasting the format of the grid
cells.) I thought I'd put in the cell contents (which will be related
to the previous week's values, eg week number, date, flexi balance) in
later steps of the procedure. Probably a long way round, but I'm a
beginner and thought that this would be a good exercise. Is this a
bad idea?

 

[robzrob]

Assuming that the Week Number is text and the Day and Date are formulas (basically, =Bn+1 and =Cn+1 where n is the preceding row number producedby copying down), this macro should do what you want (assuming I understand your layout correctly)...

Sub AddNextWeek()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Cells(LastRow, "A").EntireRow.Resize(8, 3).FillDown
  Cells(LastRow + 1, "A").Resize(7).Value = Cells(LastRow, "A").Value+ 1
End Sub

If this doesn't do what you want, then you will have to tell us, in detail, how your worksheet is laid out, what is in the various cells and what parts of them you want placed for the next week.

--
Rick (MVP - Excel)





Some text, some formulas, but I'm not really interested in pasting
what's in them, just setting up the new week grid first  - in the
right place (so I would just be pasting the format of the grid
cells.)  I thought I'd put in the cell contents (which will be related
to the previous week's values, eg week number, date, flexi balance) in
later steps of the procedure.  Probably a long way round, but I'm a
beginner and thought that this would be a good exercise.  Is this a
bad idea?- Hide quoted text -

- Show quoted text -

I've tried pasting that in 'This Workbook', 'Sheet1' (where the grid
is) and a Module. Nothing happens when I run it.

 

[Rick Rothstein]

Assuming that the Week Number is text and the Day and Date

I've tried pasting that in 'This Workbook', 'Sheet1' (where the
grid is) and a Module. Nothing happens when I run it.

Is your Week Number, Day and Date in Columns A, B and C respectively (as my
code assumes)?