Fill down formula on relative range

Serafin54

Board Regular
Joined
Apr 11, 2014
Messages
155
Office Version
  1. 2016
Platform
  1. Windows
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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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:
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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
Back
Top