Fill down formula on relative range

Serafin54

New Member
Joined
Apr 11, 2014
Messages
43
Hello.

I have a vba that I recorded. The column can change from report to report but I need the formula to fill down to the last row of data. The report could be 10 rows or 10000 so what i currently have isn't working very efficiently.

Any explanation would also be greatly appreciated.

1614892350746.png
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,372
Office Version
  1. 365
Platform
  1. Windows
Are you inserted a blank column A and then inserting this formula?
Can you please re-post your code using Code Tags?
You have posted an image, and we cannot copy/paste that to the VB Editor to save us from rewriting all your code manually.

Just copy/paste the text of the code here, then apply the Code Tags as shown here:
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
Try:

VBA Code:
Sub FindingLastRow()

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet

LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

Selection.Insert shift:=xlToRight
ActiveCell = "Time Period"
ActiveCell.Offset(1, 0).FormulaR1C1 = "=""Check Date Qtr""&ROUNDUP(MONTH(RC[1])/3,0)"
ActiveCell.Offset(1, 0).Copy Range(ActiveCell.Offset(1, 0), Cells(LastRow, ActiveCell.Column))

End Sub
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,372
Office Version
  1. 365
Platform
  1. Windows
If my original assumptions are correct, and the first formula goes in cell A2, you can simplify mrsgl9898's code a little like this:
VBA Code:
Sub Time_Period_From_Date()

    Dim LastRow As Long

    LastRow = Cells(Rows.Count, "A").End(xlUp).Row

    Columns("A:A").Insert shift:=xlToRight
    Range("A1") = "Time Period"
    Range("A2:A" & LastRow).FormulaR1C1 = "=""Check Date Qtr""&ROUNDUP(MONTH(RC[1])/3,0)"

End Sub
Note: Generally, I do not like using "ActiveCell" in VBA code, as that depends on the person being in the correct cell at the start, or else it won't work correctly.
If you want to start in a definite place, use that cell reference in your code.
 

Serafin54

New Member
Joined
Apr 11, 2014
Messages
43

ADVERTISEMENT

Try:

VBA Code:
Sub FindingLastRow()

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet

LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

Selection.Insert shift:=xlToRight
ActiveCell = "Time Period"
ActiveCell.Offset(1, 0).FormulaR1C1 = "=""Check Date Qtr""&ROUNDUP(MONTH(RC[1])/3,0)"
ActiveCell.Offset(1, 0).Copy Range(ActiveCell.Offset(1, 0), Cells(LastRow, ActiveCell.Column))

End Sub
Are you inserted a blank column A and then inserting this formula?
Can you please re-post your code using Code Tags?
You have posted an image, and we cannot copy/paste that to the VB Editor to save us from rewriting all your code manually.

Just copy/paste the text of the code here, then apply the Code Tags as shown here:
My apologies.

VBA Code:
Sub Time_Period_from_Date()
'Will get the formatted time period from the date column
    Selection.Insert Shift:=xlToRight
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "Time Period"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=""Check Date - Qtr ""&ROUNDUP(MONTH(RC[1])/3,0)"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A8999")
    ActiveCell.Range("A1:A8999").Select
End Sub

To answer your questions I'm not inserting a column. I am highlighting the column with the date and then running the vba. The column "floats" so it may never be in the same place depending on the report but the check date quarter is always adjacent to the left.
 

Serafin54

New Member
Joined
Apr 11, 2014
Messages
43
Try:

VBA Code:
Sub FindingLastRow()

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet

LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

Selection.Insert shift:=xlToRight
ActiveCell = "Time Period"
ActiveCell.Offset(1, 0).FormulaR1C1 = "=""Check Date Qtr""&ROUNDUP(MONTH(RC[1])/3,0)"
ActiveCell.Offset(1, 0).Copy Range(ActiveCell.Offset(1, 0), Cells(LastRow, ActiveCell.Column))

End Sub

This works as needed. Thank you
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951

ADVERTISEMENT

No worries
 

Serafin54

New Member
Joined
Apr 11, 2014
Messages
43
If my original assumptions are correct, and the first formula goes in cell A2, you can simplify mrsgl9898's code a little like this:
VBA Code:
Sub Time_Period_From_Date()

    Dim LastRow As Long

    LastRow = Cells(Rows.Count, "A").End(xlUp).Row

    Columns("A:A").Insert shift:=xlToRight
    Range("A1") = "Time Period"
    Range("A2:A" & LastRow).FormulaR1C1 = "=""Check Date Qtr""&ROUNDUP(MONTH(RC[1])/3,0)"

End Sub
Note: Generally, I do not like using "ActiveCell" in VBA code, as that depends on the person being in the correct cell at the start, or else it won't work correctly.
If you want to start in a definite place, use that cell reference in your code.
Ok this is much neater than my recorded macro. I'm going to need to dissect this so i can understand what is going on so i can shorten my others. when you say it depends on being in the correct cell, what do you mean?
Thank you very much btw.
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
To bastardize the 2 and help you understand.

VBA Code:
Sub FindingLastRow()

Dim LastRow As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Selection.Insert shift:=xlToRight
ActiveCell = "Time Period"
Range(ActiveCell.Offset(1, 0), Cells(LastRow, ActiveCell.Column)).FormulaR1C1 = "=""Check Date Qtr""&ROUNDUP(MONTH(RC[1])/3,0)"

End Sub
 

Forum statistics

Threads
1,136,855
Messages
5,678,143
Members
419,746
Latest member
tysonboy82

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