Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
My ref: RN0001 Find the first & last occurrence of a strings
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
My ref: RN0001 Find the first & last occurrence of a strings
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) "robzrob" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
My ref: RN0001 Find the first & last occurrence of a strings
On Aug 8, 7:27*pm, "Rick Rothstein"
wrote: 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) "robzrob" wrote in ... 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?- Hide quoted text - - 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
My ref: RN0001 Find the first & last occurrence of a strings
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) "robzrob" wrote in message ... On Aug 8, 7:27 pm, "Rick Rothstein" wrote: 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) "robzrob" wrote in ... 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?- Hide quoted text - - 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
My ref: RN0001 Find the first & last occurrence of a strings
On Aug 8, 7:59*pm, "Rick Rothstein"
wrote: 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) "robzrob" wrote in ... On Aug 8, 7:27 pm, "Rick Rothstein" wrote: 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) "robzrob" wrote in ... 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?- Hide quoted text - - 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?- 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
My ref: RN0001 Find the first & last occurrence of a strings
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 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)? -- Rick (MVP - Excel) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find and replace numeric strings in larger text strings | Excel Worksheet Functions | |||
Find Last Occurrence of Number | Excel Discussion (Misc queries) | |||
How to find number of pairs of strings from list of strings? | Excel Worksheet Functions | |||
find last occurrence | Excel Worksheet Functions |