Autofill and programming -- I'm SO confused -- please help!

[John]

hi

I get a spreadsheet in once a day that has weird dates on it
& they need to be today's date. I've figured out how to do a
macro and save it to a button... but...

I recorded a macro yesterday.. this is what came out

Range("F2").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F1001")
Range("F2:F1001").Select


and I did the same thing today... (forgetting that I'd done it yesterday)

and this is what came out

Range("F2").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F998")
Range("F2:F998").Select


I just want it to do the whole of the F column -- well, not the whole, just
the
values that already exist - and overwrite it with today() -- why does
it put in a fixed range? and more to the point... how do I get it not to?

or is there another way to do it?

please help!

thanks

-JB

 

[Dave Peterson]

It puts in what you did that time. (How would xl guess that sometimes I mean
row 998 and sometimes it meant I wanted the used cells in that column?)

but you could do something like:

dim LastCell as range
with activesheet
set lastcell = .cells(.rows.count,"F").end(xlup)
.range("F2").formula = "=today()"
.range("F2").autofill _
destination:=.range("f2",lastcell)
.range("F2",lastcell).select
end with

Alternatively, you could plop that =today() formula in the whole range in one
plop:

Dim LastCell As Range
With ActiveSheet
Set LastCell = .Cells(.Rows.Count, "F").End(xlUp)
.Range("F2", LastCell).Formula = "=today()"
End With