Answered by:
Recording a Macro, but want formula to fill down.
Question

Good day,
I have recorded a macro. I want the formula to adjust to the last row of the data sheet as the months go on. Please see below. How can I amend the coding so the formula fills down to the last row automatically?
Sub AddingFormulas()
'
' AddingFormulas Macro
'
'
ActiveCell.FormulaR1C1 = "=TEXT(RC[19],""MMMM"")"
Range("AE5").Select
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
Range("AE4").Select
Selection.AutoFill Destination:=Range("AE4:AE2398"), Type:=xlFillDefault
Range("AE4:AE23").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("AF4").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[16],Test!C[6]:C[7],2,FALSE)"
Selection.AutoFill Destination:=Range("AF4:AF2398")
Range("AF4:AF2398").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("AF:AF").EntireColumn.AutoFit
Range("AG4").Select
ActiveWindow.SmallScroll Down:=9
ActiveCell.FormulaR1C1 = "=RC[1]&RC[2]"
Range("AG4").Select
Selection.AutoFill Destination:=Range("AG4:AG2398")
Range("AG4:AG2398").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("AG4").Select
End Sub
I would appreciate it if you could help.
Thank you.
 Moved by Edward8520Microsoft contingent staff Thursday, November 10, 2016 10:38 AM excel related
Answers

Hi,
In your excel sheet in any cell use this following formula to count the number of records in your data sheet by using this following formula:
=COUNTA(A:A) (use the column which will always have a value so that count is accurate)
Now use the above cell value in your macro to use it part of Formula fill
Sub AddingFormulas() Dim TotalNoOfRecord TotalNoOfRecord = Range("B1").Value 'this is the cell address where you have put the formula to count the records Range("AE4").Activate ActiveCell.Formula = "=TEXT(L4,""MMMM"")" Range("AE4").Select Selection.AutoFill Destination:=Range("AE4:AE" & 4 + TotalNoOfRecord), Type:=xlFillDefault Range("AF4").Activate ActiveCell.Formula = "=vlookup(P4,'Test'!AE:AF,2,false" Range("AF4").Select Selection.AutoFill Destination:=Range("AF4:AF" & 4 + TotalNoOfRecord), Type:=xlFillDefault Range("AG4").Activate ActiveCell.Formula = "=AF4&AE4" Range("AG4").Select Selection.AutoFill Destination:=Range("AG4:AG" & 4 + TotalNoOfRecord), Type:=xlFillDefault End Sub
Vish Mishra
 Marked as answer by Pikkie1234 Monday, November 7, 2016 9:28 AM
All replies

Hi Rather sharing this code, can you please share the sheet like till where and which formula you want to copy.
Since this is recorded macro... it has recorded all the movement you have done on the screen which you do not want to do every time.
Knowing your exact requirement I would be able to help you with the macro.
Vish Mishra

Good day,
I am not sure how to share my sheet. So I will explain what I want.
I have a data sheet that ranges from Column A  AC. Every month the row count will be different as the sheet gets bigger month by month.
In Column L I have a date 2016/01/23, but I would like it to display the month (January) in column AE. (With the formula =text(L1,"MMMM"))
In Column P I have a team name Silvestre Oceanus (PRL) that I replace with just Silvestre Oceanus in column AF. (Using a Vlookup formula from the Data Sheet to the test data sheet (=vlookup(P1,'Test'!AE:AF,2,false).
Then lastly I concatenate column AE and AF. Using (=AF1&AE1).
I have created a button to add all these formulas. So when I push the button I want the formulas to go down to the last row of the data sheet. As this month the data sheet might only be up to row 122 but next month it could be up to 231.
Hope it makes sense.
Thank you.

Hi,
In your excel sheet in any cell use this following formula to count the number of records in your data sheet by using this following formula:
=COUNTA(A:A) (use the column which will always have a value so that count is accurate)
Now use the above cell value in your macro to use it part of Formula fill
Sub AddingFormulas() Dim TotalNoOfRecord TotalNoOfRecord = Range("B1").Value 'this is the cell address where you have put the formula to count the records Range("AE4").Activate ActiveCell.Formula = "=TEXT(L4,""MMMM"")" Range("AE4").Select Selection.AutoFill Destination:=Range("AE4:AE" & 4 + TotalNoOfRecord), Type:=xlFillDefault Range("AF4").Activate ActiveCell.Formula = "=vlookup(P4,'Test'!AE:AF,2,false" Range("AF4").Select Selection.AutoFill Destination:=Range("AF4:AF" & 4 + TotalNoOfRecord), Type:=xlFillDefault Range("AG4").Activate ActiveCell.Formula = "=AF4&AE4" Range("AG4").Select Selection.AutoFill Destination:=Range("AG4:AG" & 4 + TotalNoOfRecord), Type:=xlFillDefault End Sub
Vish Mishra
 Marked as answer by Pikkie1234 Monday, November 7, 2016 9:28 AM

Good day,
Thank you for your reply. I have added the code and it works, but now it only goes up to row 2329 but my data goes all the way down to row 2398. I have even copied the format from the rows above to the ones below that the formula didn't go to, but it doesn't go all the way down. Nothing seems funny and the columns my formulas are referring to has the correct data in it.
Hope to hear from you soon.
Thank you

