| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofill dates when 1 is entered in a cell
If a 1 is entered in any of C7 - C13, I would like to autofill sequential
numbers down column C up as far as the number 31. It's for a monthly sheet with days of the week in column B. So regardless of which day is the 1st, the code will copy the numbers down for the user. Thanks, -- Traa Dy Liooar Jock |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill dates when 1 is entered in a cell
Jock,
Copy the code below into the codemodule of the ThisWorkbook object. If you didn't understand that line, then follow the advice from David McRitchie: "Unlike standard macros which are installed in standard modules, Workbook Events are installed in ThisWorkBook in the following manner: F11 (Visual Basic Editor), Get into your project library (name of your workbook) with Ctrl+R (View, Project Explorer), under the name of your workbook you see Microsoft Excel Objects, then before Modules you see ThisWorkBook, doubleclick and paste the code into the code window (F7)." HTH, Bernie MS Excel MVP Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Intersect(Target, Sh.Range("C7:C13")) Is Nothing Then Exit Sub If Target.Cells.Count 1 Then Exit Sub If Target.Value < 1 Then Exit Sub Application.EnableEvents = False Target.AutoFill Destination:=Target.Resize( _ Day(DateSerial(Year(Now), Month(Now) + 1, 0)), 1), _ Type:=xlFillSeries Application.EnableEvents = True End Sub "Jock" wrote in message ... If a 1 is entered in any of C7 - C13, I would like to autofill sequential numbers down column C up as far as the number 31. It's for a monthly sheet with days of the week in column B. So regardless of which day is the 1st, the code will copy the numbers down for the user. Thanks, -- Traa Dy Liooar Jock |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill dates when 1 is entered in a cell
Great Bernie, thanks. Works better than expected.
Should a user enter the '1' in the wrong cell then proceed to enter it in the correct cell, can your code be 'tweaked' to clear down column C7:C43 before autofilling from the new '1' thus removing numbers which would otherwise be outside the autofill range and would not be overwritten? -- Traa Dy Liooar Jock "Bernie Deitrick" wrote: Jock, Copy the code below into the codemodule of the ThisWorkbook object. If you didn't understand that line, then follow the advice from David McRitchie: "Unlike standard macros which are installed in standard modules, Workbook Events are installed in ThisWorkBook in the following manner: F11 (Visual Basic Editor), Get into your project library (name of your workbook) with Ctrl+R (View, Project Explorer), under the name of your workbook you see Microsoft Excel Objects, then before Modules you see ThisWorkBook, doubleclick and paste the code into the code window (F7)." HTH, Bernie MS Excel MVP Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Intersect(Target, Sh.Range("C7:C13")) Is Nothing Then Exit Sub If Target.Cells.Count 1 Then Exit Sub If Target.Value < 1 Then Exit Sub Application.EnableEvents = False Target.AutoFill Destination:=Target.Resize( _ Day(DateSerial(Year(Now), Month(Now) + 1, 0)), 1), _ Type:=xlFillSeries Application.EnableEvents = True End Sub "Jock" wrote in message ... If a 1 is entered in any of C7 - C13, I would like to autofill sequential numbers down column C up as far as the number 31. It's for a monthly sheet with days of the week in column B. So regardless of which day is the 1st, the code will copy the numbers down for the user. Thanks, -- Traa Dy Liooar Jock |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill dates when 1 is entered in a cell
Jock,
Try this version... HTH, Bernie MS Excel MVP Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Intersect(Target, Sh.Range("C7:C13")) Is Nothing Then Exit Sub If Target.Cells.Count 1 Then Exit Sub If Target.Value < 1 Then Exit Sub Application.EnableEvents = False 'Clear old stuff below Target.Offset(1).Resize(43 - Target.Row).ClearContents 'Clear old stuff above If Target.Row < 7 Then Target.Offset(7 - Target.Row).Resize(Target.Row - 7).ClearContents End If Target.AutoFill Destination:=Target.Resize( _ Day(DateSerial(Year(Now), Month(Now) + 1, 0)), 1), _ Type:=xlFillSeries Application.EnableEvents = True End Sub "Jock" wrote in message ... Great Bernie, thanks. Works better than expected. Should a user enter the '1' in the wrong cell then proceed to enter it in the correct cell, can your code be 'tweaked' to clear down column C7:C43 before autofilling from the new '1' thus removing numbers which would otherwise be outside the autofill range and would not be overwritten? -- Traa Dy Liooar Jock "Bernie Deitrick" wrote: Jock, Copy the code below into the codemodule of the ThisWorkbook object. If you didn't understand that line, then follow the advice from David McRitchie: "Unlike standard macros which are installed in standard modules, Workbook Events are installed in ThisWorkBook in the following manner: F11 (Visual Basic Editor), Get into your project library (name of your workbook) with Ctrl+R (View, Project Explorer), under the name of your workbook you see Microsoft Excel Objects, then before Modules you see ThisWorkBook, doubleclick and paste the code into the code window (F7)." HTH, Bernie MS Excel MVP Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Intersect(Target, Sh.Range("C7:C13")) Is Nothing Then Exit Sub If Target.Cells.Count 1 Then Exit Sub If Target.Value < 1 Then Exit Sub Application.EnableEvents = False Target.AutoFill Destination:=Target.Resize( _ Day(DateSerial(Year(Now), Month(Now) + 1, 0)), 1), _ Type:=xlFillSeries Application.EnableEvents = True End Sub "Jock" wrote in message ... If a 1 is entered in any of C7 - C13, I would like to autofill sequential numbers down column C up as far as the number 31. It's for a monthly sheet with days of the week in column B. So regardless of which day is the 1st, the code will copy the numbers down for the user. Thanks, -- Traa Dy Liooar Jock |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill dates when 1 is entered in a cell
Fantastic Bernie, thanks!
-- Traa Dy Liooar Jock "Bernie Deitrick" wrote: Jock, Try this version... HTH, Bernie MS Excel MVP Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Intersect(Target, Sh.Range("C7:C13")) Is Nothing Then Exit Sub If Target.Cells.Count 1 Then Exit Sub If Target.Value < 1 Then Exit Sub Application.EnableEvents = False 'Clear old stuff below Target.Offset(1).Resize(43 - Target.Row).ClearContents 'Clear old stuff above If Target.Row < 7 Then Target.Offset(7 - Target.Row).Resize(Target.Row - 7).ClearContents End If Target.AutoFill Destination:=Target.Resize( _ Day(DateSerial(Year(Now), Month(Now) + 1, 0)), 1), _ Type:=xlFillSeries Application.EnableEvents = True End Sub "Jock" wrote in message ... Great Bernie, thanks. Works better than expected. Should a user enter the '1' in the wrong cell then proceed to enter it in the correct cell, can your code be 'tweaked' to clear down column C7:C43 before autofilling from the new '1' thus removing numbers which would otherwise be outside the autofill range and would not be overwritten? -- Traa Dy Liooar Jock "Bernie Deitrick" wrote: Jock, Copy the code below into the codemodule of the ThisWorkbook object. If you didn't understand that line, then follow the advice from David McRitchie: "Unlike standard macros which are installed in standard modules, Workbook Events are installed in ThisWorkBook in the following manner: F11 (Visual Basic Editor), Get into your project library (name of your workbook) with Ctrl+R (View, Project Explorer), under the name of your workbook you see Microsoft Excel Objects, then before Modules you see ThisWorkBook, doubleclick and paste the code into the code window (F7)." HTH, Bernie MS Excel MVP Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Intersect(Target, Sh.Range("C7:C13")) Is Nothing Then Exit Sub If Target.Cells.Count 1 Then Exit Sub If Target.Value < 1 Then Exit Sub Application.EnableEvents = False Target.AutoFill Destination:=Target.Resize( _ Day(DateSerial(Year(Now), Month(Now) + 1, 0)), 1), _ Type:=xlFillSeries Application.EnableEvents = True End Sub "Jock" wrote in message ... If a 1 is entered in any of C7 - C13, I would like to autofill sequential numbers down column C up as far as the number 31. It's for a monthly sheet with days of the week in column B. So regardless of which day is the 1st, the code will copy the numbers down for the user. Thanks, -- Traa Dy Liooar Jock |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| autofill colour based on date entered | Excel Discussion (Misc queries) | |||
| How can I autofill dates having a blank cell between each day? | New Users to Excel | |||
| autofill on fixed dates | Excel Worksheet Functions | |||
| autofill next sheet with next set of dates | Excel Worksheet Functions | |||
| If both dates are entered into a cell then answer is yes | Excel Worksheet Functions | |||
Hybrid Mode
