Excel 2000

I have an imported file with data in column AB. I enter a 'post date' in
column AC, row 1, but when I highlight the cell and double click on the box,
the date increases as it goes down the column. When I hold down the CTRL key
and double click on the box, it also increases as it goes down the column.
If I manually draw AC1 down with the CTRL key pressed, it works the way I
want - i.e. 02/29/08 is in each cell in the column. Is there a macro I
should use?

TIA,
CaroleO

hi
is there a reason you can't just copy and paste?

confused
FSt1

"Carole O" wrote:

Excel 2000

I have an imported file with data in column AB. I enter a 'post date' in
column AC, row 1, but when I highlight the cell and double click on the box,
the date increases as it goes down the column. When I hold down the CTRL key
and double click on the box, it also increases as it goes down the column.
If I manually draw AC1 down with the CTRL key pressed, it works the way I
want - i.e. 02/29/08 is in each cell in the column. Is there a macro I
should use?

TIA,
CaroleO

I'm designing the program for an assistant. There are several hundred rows
and 8 spreadsheets. It would be much quicker for her to enter in the date,
click and have the cells populate the column. Any ideas?

"Carole O" wrote:

Excel 2000

I have an imported file with data in column AB. I enter a 'post date' in
column AC, row 1, but when I highlight the cell and double click on the box,
the date increases as it goes down the column. When I hold down the CTRL key
and double click on the box, it also increases as it goes down the column.
If I manually draw AC1 down with the CTRL key pressed, it works the way I
want - i.e. 02/29/08 is in each cell in the column. Is there a macro I
should use?

TIA,
CaroleO

Carole,

This is a "benefit" programmed into XL called fill series. XL thinks you
want to increment the date by 1 because that is the norm. There is no way
to bypass it with a key combination that I'm aware of.

A couple of work arounds:

1. Do your fill as usuall by double-clicking the fill handle. That will
fill series down to the first blank cell in the adjacent column and should
leave all filled cells selected, including the first cell you filled from.
With all of the date cells selected, do a fill down (Edit menu Fill Down
or [Ctrl] + D). That will copy the contents from the top cell down through
the whole selection.

2. Enter the date in AC1. Enter the formula "=$AC$1" (w/o the quotes) in
cell AC2. Double-click the fill handle.

HTH,

Conan




"Carole O" wrote in message
...
I'm designing the program for an assistant. There are several hundred
rows
and 8 spreadsheets. It would be much quicker for her to enter in the
date,
click and have the cells populate the column. Any ideas?

"Carole O" wrote:

Excel 2000

I have an imported file with data in column AB. I enter a 'post date' in
column AC, row 1, but when I highlight the cell and double click on the
box,
the date increases as it goes down the column. When I hold down the CTRL
key
and double click on the box, it also increases as it goes down the
column.
If I manually draw AC1 down with the CTRL key pressed, it works the way I
want - i.e. 02/29/08 is in each cell in the column. Is there a macro I
should use?

TIA,
CaroleO

Up for some VBA?

Probably some better code out there but this does work.

Sub Auto_Fill()
Dim Lrow As Long
With ActiveSheet
Lrow = Cells(Rows.Count, ActiveCell.Offset _
(0, -1).Column).End(xlUp).Row
Range(ActiveCell.Offset(-1, 0).Address & ":" & _
GetColLet(ActiveCell.Column) & Lrow).FillDown
End With
End Sub

Function GetColLet(ColNumber As Integer) As String
GetColLet = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber 26))
End Function

Copy both the Sub and the Function to a general module in your workbook.

Assign Sub Auto_Fill() to a button or shortcut key.

Enter a date in any cell of any column then hit a button to copy down to bottom
of left-adjacent column.

Assumes "move selection after enter" is set to down.


Gord Dibben MS Excel MVP


On Thu, 28 Feb 2008 20:09:05 -0800, Carole O
wrote:

I'm designing the program for an assistant. There are several hundred rows
and 8 spreadsheets. It would be much quicker for her to enter in the date,
click and have the cells populate the column. Any ideas?

"Carole O" wrote:

Excel 2000

I have an imported file with data in column AB. I enter a 'post date' in
column AC, row 1, but when I highlight the cell and double click on the box,
the date increases as it goes down the column. When I hold down the CTRL key
and double click on the box, it also increases as it goes down the column.
If I manually draw AC1 down with the CTRL key pressed, it works the way I
want - i.e. 02/29/08 is in each cell in the column. Is there a macro I
should use?

TIA,
CaroleO

Thanks, Gord!! That was exactly what I was looking for - works great!!

CaroleO

"Gord Dibben" wrote:

Up for some VBA?

Probably some better code out there but this does work.

Sub Auto_Fill()
Dim Lrow As Long
With ActiveSheet
Lrow = Cells(Rows.Count, ActiveCell.Offset _
(0, -1).Column).End(xlUp).Row
Range(ActiveCell.Offset(-1, 0).Address & ":" & _
GetColLet(ActiveCell.Column) & Lrow).FillDown
End With
End Sub

Function GetColLet(ColNumber As Integer) As String
GetColLet = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber 26))
End Function

Copy both the Sub and the Function to a general module in your workbook.

Assign Sub Auto_Fill() to a button or shortcut key.

Enter a date in any cell of any column then hit a button to copy down to bottom
of left-adjacent column.

Assumes "move selection after enter" is set to down.


Gord Dibben MS Excel MVP


On Thu, 28 Feb 2008 20:09:05 -0800, Carole O
wrote:

I'm designing the program for an assistant. There are several hundred rows
and 8 spreadsheets. It would be much quicker for her to enter in the date,
click and have the cells populate the column. Any ideas?

"Carole O" wrote:

Excel 2000

I have an imported file with data in column AB. I enter a 'post date' in
column AC, row 1, but when I highlight the cell and double click on the box,
the date increases as it goes down the column. When I hold down the CTRL key
and double click on the box, it also increases as it goes down the column.
If I manually draw AC1 down with the CTRL key pressed, it works the way I
want - i.e. 02/29/08 is in each cell in the column. Is there a macro I
should use?

TIA,
CaroleO

Thanks for the feedback.

Gord

On Wed, 5 Mar 2008 08:49:00 -0800, Carole O
wrote:

Thanks, Gord!! That was exactly what I was looking for - works great!!

CaroleO

"Gord Dibben" wrote:

Up for some VBA?

Probably some better code out there but this does work.

Sub Auto_Fill()
Dim Lrow As Long
With ActiveSheet
Lrow = Cells(Rows.Count, ActiveCell.Offset _
(0, -1).Column).End(xlUp).Row
Range(ActiveCell.Offset(-1, 0).Address & ":" & _
GetColLet(ActiveCell.Column) & Lrow).FillDown
End With
End Sub

Function GetColLet(ColNumber As Integer) As String
GetColLet = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber 26))
End Function

Copy both the Sub and the Function to a general module in your workbook.

Assign Sub Auto_Fill() to a button or shortcut key.

Enter a date in any cell of any column then hit a button to copy down to bottom
of left-adjacent column.

Assumes "move selection after enter" is set to down.


Gord Dibben MS Excel MVP


On Thu, 28 Feb 2008 20:09:05 -0800, Carole O
wrote:

I'm designing the program for an assistant. There are several hundred rows
and 8 spreadsheets. It would be much quicker for her to enter in the date,
click and have the cells populate the column. Any ideas?

"Carole O" wrote:

Excel 2000

I have an imported file with data in column AB. I enter a 'post date' in
column AC, row 1, but when I highlight the cell and double click on the box,
the date increases as it goes down the column. When I hold down the CTRL key
and double click on the box, it also increases as it goes down the column.
If I manually draw AC1 down with the CTRL key pressed, it works the way I
want - i.e. 02/29/08 is in each cell in the column. Is there a macro I
should use?

TIA,
CaroleO