VBA - drag down formulas to today's date

rowbro

New Member
Joined
Dec 16, 2010
Messages
37
Hi there - I am fairly new to VBA, having mainly recorded macros (and tinkered with them a little) in the past. I am now trying to drag formulas in the final row of a worksheet (columns A:R) from the final date in the worksheet to today's date (dates in the A column). However, when I try this using a recorded macro it takes the absolute location of a cell, and so drags down the same row every time. Is there some way to select the LAST ROW of data and drag it down X cells, where X is the difference between today's date and the last date in the A column?

Thanks!
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Arithos

Well-known Member
Joined
Aug 14, 2014
Messages
598
Hi there - I am fairly new to VBA, having mainly recorded macros (and tinkered with them a little) in the past. I am now trying to drag formulas in the final row of a worksheet (columns A:R) from the final date in the worksheet to today's date (dates in the A column). However, when I try this using a recorded macro it takes the absolute location of a cell, and so drags down the same row every time. Is there some way to select the LAST ROW of data and drag it down X cells, where X is the difference between today's date and the last date in the A column?
Thanks!
Yes, there is, please post your code in the future, then it will be easier to help.

You can identify the last row in a column by this command.

Code:
'Create a varialbe, "LastRow" or simply "LR"(which I normally use)
[COLOR=#ff0000]LastRow = Sheets("YourSheetHere").cells(rows.count, 1).end(xlup).row[/COLOR]


'cells(rows.count, [COLOR=#ff0000]1[/COLOR]) 1 refers to column 1, in the worksheet

'This will return the row of the first cell in column A, starting from the bottom going upward.
You can implement using this "LR" in your already created code.

I think its something like

Code:
selection.fill(something of a range)

'just change your range like this
'assuming formulas go in Column R
[COLOR=#ff0000]range("R1:R" & LastRow)[/COLOR]
just use the "LastRow" variable.

Again, if I had your code for inserting your formula I could show you how to do this straight away. (simpler yes?)
 

rowbro

New Member
Joined
Dec 16, 2010
Messages
37
Thanks Arithos, something isn't working for me (suspect I am the problem) - my code is as follows, could you please advise as to how to incorporate what you suggested?

Code:
' INET Macro
'

'
    Range("A1128:R1128").Select
    Selection.AutoFill Destination:=Range("A1128:R1129"), Type:=xlFillDefault
    Range("A1128:R1129").Select
End Sub
 

Arithos

Well-known Member
Joined
Aug 14, 2014
Messages
598
Thanks Arithos, something isn't working for me (suspect I am the problem) - my code is as follows, could you please advise as to how to incorporate what you suggested?

Code:
' INET Macro
'

'
    Range("A1128:R1128").Select
    Selection.AutoFill Destination:=Range("A1128:R1129"), Type:=xlFillDefault
    Range("A1128:R1129").Select
End Sub
Something in the code you posted seems strange =P
Not sure exactly what kind of formula etc you are using, but from my understanding, the below is what you should use. Rememeber to replace "YourSheetHere" with the sheet name your data is located

Code:
dim Sht as worksheet

set Sht = Sheets("[COLOR=#0000ff]YourSheetHere[/COLOR]")

[COLOR=#FF0000]LastRow = Sht.cells(rows.count, 1).end(xlup).row
[/COLOR]LR1 = LastRow - 1
Range([COLOR=#ff0000]"A" & LR1 & ":R" & LastRow[/COLOR]).Select    
Selection.AutoFill Destination:=Range([COLOR=#ff0000]"A" & LR1 & ":R" & LastRow[/COLOR]), Type:=xlFillDefault[COLOR=#FF0000]
[/COLOR]
 

Arithos

Well-known Member
Joined
Aug 14, 2014
Messages
598
A recorded formula insertion, and autofill would look like this.

Code:
Sub Macro1()

[COLOR=#FF0000]LR = Sht.cells(rows.count, 1).end(xlup).row    [/COLOR]'see reference from previous post
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]+R[1]C[-1]"
Selection.AutoFill Destination:=Range("B1:B" [COLOR=#ff0000]& LR[/COLOR]), Type:=xlFillDefault


End Sub
Like this, you can only ammend the autofill range.. marked in RED above

Yours looks different..
 

rowbro

New Member
Joined
Dec 16, 2010
Messages
37
I really appreciate the assistance here, but I can't seem to get it to work. I am not sure if I explained what I wanted to do properly -

I have a lot of formulas in rows from column A to column R. In column A, are dates. I would like to use VBA to drag the last row's formulas down from the latest date in column A, to today's date. Thus, if the last date is the 1st of September, and today is the 29th of September, I would like it to drag the formulas (in the last row) down a further 28 rows.

Is this possible?
 

Arithos

Well-known Member
Joined
Aug 14, 2014
Messages
598
I really appreciate the assistance here, but I can't seem to get it to work. I am not sure if I explained what I wanted to do properly -

I have a lot of formulas in rows from column A to column R. In column A, are dates. I would like to use VBA to drag the last row's formulas down from the latest date in column A, to today's date. Thus, if the last date is the 1st of September, and today is the 29th of September, I would like it to drag the formulas (in the last row) down a further 28 rows.

Is this possible?

Right, I think I know where I went wrong when showing you the code.


Code:
sub YourMacro()
dim Sht as worksheet


set Sht = Sheets("[COLOR=#00ff00]YourSheetHere[/COLOR]")


LastRow = Sht.cells(rows.count, 1).end(xlup).row
LR1 = LastRow +[COLOR=#ff0000]28[/COLOR]
Range("A" & [COLOR=#ff0000]LastRow [/COLOR]& ":R" & [COLOR=#ff0000]LastRow[/COLOR]).Select    
Selection.AutoFill Destination:=Range("A" & [COLOR=#ff0000]LastRow [/COLOR]& ":R" & [COLOR=#0000ff]LR1[/COLOR]), Type:=xlFillDefault


End sub

28 can be altered to fill in further, it will change from month to month I will assume? You could make this dynamic aswell, but first, just check if this works :)

it will copy formulas from the last row found in the sheet, and paste 28 rows down..
 

rowbro

New Member
Joined
Dec 16, 2010
Messages
37
Thanks, yes, that works. Is there some way to make it dynamic so that it adds, rather than 28, (today-last date in column A)?

Thanks!
 

Arithos

Well-known Member
Joined
Aug 14, 2014
Messages
598
Thanks, yes, that works. Is there some way to make it dynamic so that it adds, rather than 28, (today-last date in column A)?

Thanks!
yes it is

Code:
DaysInMonth = [DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))]
MsgBox DaysInMonth
This gives DaysInMonth = 30, since it uses todays date, if you want it even more controllable by user, do like this

you could add the values yourself, like this.

Code:
Sub Test()
    Dim m As Integer, y As Integer, DaysInMonth As Integer
    ' define variable for number of the month, in this example, September
    m = InputBox("What month is this? 9=september?")
    ' define variable for the year
    y = InputBox("What year is this?, 2014?")' 
    DaysInMonth = DateSerial(y, m + 1, 1) - DateSerial(y, m, 1)
    MsgBox DaysInMonth
End Sub

Your new complete SUB will then look like this

Code:
sub YourMacro()
dim Sht as worksheet

Dim m As Integer, y As Integer, DaysInMonth As Integer
    m = InputBox("What month is this? 9=september?")
    y = InputBox("What year is this?, 2014?")' if you are using 2014 only, then just type in 2014 here
    [COLOR=#ff0000]DaysInMonth [/COLOR]= DateSerial(y, m + 1, 1) - DateSerial(y, m, 1)

set Sht = Sheets("YourSheetHere")

LastRow = Sht.cells(rows.count, 1).end(xlup).row
[COLOR=#00ff00]LR1[/COLOR] = LastRow + [COLOR=#ff0000]DaysInMonth[/COLOR]
Range("A" & LastRow & ":R" & LastRow).Select    
Selection.AutoFill Destination:=Range("A" & LastRow & ":R" &[COLOR=#00ff00] LR1[/COLOR]), Type:=xlFillDefault

End sub

Will this suffice? If you will not use different years then 2014, you can just replace

Code:
 y = InputBox("What year is this?, 2014?")
with
Code:
y = 2014
 
Last edited:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,036
Messages
5,508,922
Members
408,701
Latest member
Ucchik7

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top