Hi All,

I have a spreadsheet that will contain varying amounts of data (lets say
between 20,000 and 40,000) records. I have created a macro that will sort
this data into a specific format. Now i want to just put todays data against
every record. (So basically i want "=today()" all down column A.) I also
dont want more than I need. So if i have 20,000 records I only want 20,000
dates pasted in.

I have tried using "record a macro" whilst recording I use:
"end + down arrow" to find the last record then shift accross to the left,
then
"shift + end + up arrow" Then pasting in the date. However record a macro,
just fills in the cells A1:A20000. So if I then have a sheet with 30,000
records im missing a lot of dates.

Anyone have an idea?

Thanks in advance, any help is much appreciated :)

Ernest

ps. have a good weekend

Suppose you want to "fill down" column A, but only as far as there is data in
column B

In cell A1 enter:
=IF(B1="","",TODAY())

and copy all the way down. As soon as there is no data in column B, column
A will also show blank. This way you don't need to worry about where your
records end.
--
Gary's Student
gsnu200702


"Ernest Lai" wrote:

Hi All,

I have a spreadsheet that will contain varying amounts of data (lets say
between 20,000 and 40,000) records. I have created a macro that will sort
this data into a specific format. Now i want to just put todays data against
every record. (So basically i want "=today()" all down column A.) I also
dont want more than I need. So if i have 20,000 records I only want 20,000
dates pasted in.

I have tried using "record a macro" whilst recording I use:
"end + down arrow" to find the last record then shift accross to the left,
then
"shift + end + up arrow" Then pasting in the date. However record a macro,
just fills in the cells A1:A20000. So if I then have a sheet with 30,000
records im missing a lot of dates.

Anyone have an idea?

Thanks in advance, any help is much appreciated :)

Ernest

ps. have a good weekend

Thanks very much :D works perfectly and explained much better haha. And I
thought Friday couldnt get any better.

thanks again.

Ernest




"Gary''s Student" wrote:

Suppose you want to "fill down" column A, but only as far as there is data in
column B

In cell A1 enter:
=IF(B1="","",TODAY())

and copy all the way down. As soon as there is no data in column B, column
A will also show blank. This way you don't need to worry about where your
records end.
--
Gary's Student
gsnu200702


"Ernest Lai" wrote:

Hi All,

I have a spreadsheet that will contain varying amounts of data (lets say
between 20,000 and 40,000) records. I have created a macro that will sort
this data into a specific format. Now i want to just put todays data against
every record. (So basically i want "=today()" all down column A.) I also
dont want more than I need. So if i have 20,000 records I only want 20,000
dates pasted in.

I have tried using "record a macro" whilst recording I use:
"end + down arrow" to find the last record then shift accross to the left,
then
"shift + end + up arrow" Then pasting in the date. However record a macro,
just fills in the cells A1:A20000. So if I then have a sheet with 30,000
records im missing a lot of dates.

Anyone have an idea?

Thanks in advance, any help is much appreciated :)

Ernest

ps. have a good weekend

Ernest

You do realize that tomorrow the =TODAY() function will update.

Here's a macro to insertcopy the date from A1 down as far as you have data in
Column B

Sub Auto_Fill()
Dim Lrow As Long
With ActiveSheet
Lrow = Range("B" & Rows.Count).End(xlUp).Row
Range("A1").Value = Format(Date, "mm-dd-yyyy")
'or if you want =TODAY()
'Range("A1").Formula = "=TODAY()"
Range("A1:A" & Lrow).FillDown
End With
End Sub


Gord Dibben MS Excel MVP



On Fri, 26 Jan 2007 08:21:02 -0800, Ernest Lai
wrote:

Hi All,

I have a spreadsheet that will contain varying amounts of data (lets say
between 20,000 and 40,000) records. I have created a macro that will sort
this data into a specific format. Now i want to just put todays data against
every record. (So basically i want "=today()" all down column A.) I also
dont want more than I need. So if i have 20,000 records I only want 20,000
dates pasted in.

I have tried using "record a macro" whilst recording I use:
"end + down arrow" to find the last record then shift accross to the left,
then
"shift + end + up arrow" Then pasting in the date. However record a macro,
just fills in the cells A1:A20000. So if I then have a sheet with 30,000
records im missing a lot of dates.

Anyone have an idea?

Thanks in advance, any help is much appreciated :)

Ernest

ps. have a good weekend

Ooo Thanks Gord, that actually works a lil better. As I import the file
into access afterwards and now i dont get the import errors (which didnt
really matter as its not in the same table as the data)

Yes I am aware Today() updates I did a macro to special paste the values
afterwards but I guess that wasnt very efficient ;)

Thanks again.




"Gord Dibben" wrote:

Ernest

You do realize that tomorrow the =TODAY() function will update.

Here's a macro to insertcopy the date from A1 down as far as you have data in
Column B

Sub Auto_Fill()
Dim Lrow As Long
With ActiveSheet
Lrow = Range("B" & Rows.Count).End(xlUp).Row
Range("A1").Value = Format(Date, "mm-dd-yyyy")
'or if you want =TODAY()
'Range("A1").Formula = "=TODAY()"
Range("A1:A" & Lrow).FillDown
End With
End Sub


Gord Dibben MS Excel MVP



On Fri, 26 Jan 2007 08:21:02 -0800, Ernest Lai
wrote:

Hi All,

I have a spreadsheet that will contain varying amounts of data (lets say
between 20,000 and 40,000) records. I have created a macro that will sort
this data into a specific format. Now i want to just put todays data against
every record. (So basically i want "=today()" all down column A.) I also
dont want more than I need. So if i have 20,000 records I only want 20,000
dates pasted in.

I have tried using "record a macro" whilst recording I use:
"end + down arrow" to find the last record then shift accross to the left,
then
"shift + end + up arrow" Then pasting in the date. However record a macro,
just fills in the cells A1:A20000. So if I then have a sheet with 30,000
records im missing a lot of dates.

Anyone have an idea?

Thanks in advance, any help is much appreciated :)

Ernest

ps. have a good weekend