Copy last formula in column and past into range

**mtbe** · 08-27-2009, 05:35 PM

Every time you guys are able to get me over a hurdle, I think of something else.

Document is attached.

Cells with Formula starting with "=AT" in attached file is getting data from another source. The last time the file was opened was Jan 14, so the data was good up to Jan 13 (although you will just see errors).

Currently, the formula is in every cell for the whole year, however I don't want the the formula to search for the data, if the date hasn't passed. It's incredibly time consuming. (I removed the formula in the columns past Jan 13 to indicate what I'm looking for).

So, when I open the file on Jan 16, I need to find the columns with "=AT" in the formula, find the last cell in each of those columns, copy it, select the range from that date (the last date the file was open) to today, and paste the formula.

And the same if I open it on Jan 21. Find the last time it was updated, and fill in the appropiate cells, up to Jan 21.

Essentially, I want to fill the blank cells with formula when I run the macro, but only up to the current date.

**Leith Ross** · 08-27-2009, 06:40 PM

Hello mtbe,

This macro will fill in the formulae in columns "C,E,G". It finds the last update date and fills in the range up to today.

Sub AutoFormulaFill()

  Dim D1 As Date
  Dim D2 As Date
  Dim LastRow As Long
  Dim NextRow As Long
    
    D1 = "01/01/2009"
    D2 = Now()
    
    NextRow = DateDiff("d", D1, D2) + 3
    LastRow = Cells(Rows.Count, "C").End(xlUp).Row
    
    Application.ScreenUpdating = False
      If LastRow > 3 Then
         Range(Cells(LastRow, "C"), Cells(NextRow, "C")).FillDown
         Range(Cells(LastRow, "E"), Cells(NextRow, "E")).FillDown
         Range(Cells(LastRow, "G"), Cells(NextRow, "G")).FillDown
      End If
    Application.ScreenUpdating = True
    
End Sub

**mtbe** · 08-27-2009, 09:00 PM

Thanks!

Since this was a sample file, my file is much bigger.

Two things:

At the end of the Columns C, E, and G I have other things going on (not the same formula). So the LastRow = Cells(Rows.Count, "C").End(xlUp).Row may not find the correct cell.

Would this work: LastRow = Cells(Rows.Count, "C").End(xlDown).Offset(1,0).Row to select the first empty cell in the column?

I have multiple sheets and most are much bigger than just 6-8 columns. The number of columns on each sheet that need to be changed ranges from 4 to 24. And they columns I need changed are not always seperated by just one column. How would I choose the columns I need to change (the cells in the columns I need to change/work will all have a formula that starts with "=AT".

Wow...when I preview this, the text is out of whack...sorry about that. Don't know what's going on....