VBA - drag down formulas to today's date

rowbro

New Member
Joined
Dec 16, 2010
Messages
43
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!
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

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
43
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

ADVERTISEMENT

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
43
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

ADVERTISEMENT

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
43
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:

Watch MrExcel Video

Forum statistics

Threads
1,132,818
Messages
5,655,465
Members
418,204
Latest member
ElizabethCorrin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top