Date drawdown questions

C

Carole O

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
 
C

Carole O

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?
 
C

Conan Kelly

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
 
G

Gord Dibben

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
 
C

Carole O

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

CaroleO
 
Top