VBA - drag down formulas to today's date

[rowbro]

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!

 

[Arithos]

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.

'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

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]

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?

' INET Macro
'

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

 

[Arithos]

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?

' 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

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]

A recorded formula insertion, and autofill would look like this.

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]

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]

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.

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]

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]

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

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.

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

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

 y = InputBox("What year is this?, 2014?")

with

y = 2014

 

[rowbro]

Awesome. Thanks for all the help.