none
Recording a Macro, but want formula to fill down. RRS feed

  • 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.

    Tuesday, November 1, 2016 12:22 PM

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
    Tuesday, November 1, 2016 2:05 PM

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

    Tuesday, November 1, 2016 12:35 PM
  • 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.

    Tuesday, November 1, 2016 1:23 PM
  • 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
    Tuesday, November 1, 2016 2:05 PM
  • 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

    Monday, November 7, 2016 9:05 AM
  • Hi,

    What is the record count are you getting from the formula : COUNTA(A:A) ??


    Vish Mishra

    Monday, November 7, 2016 9:15 AM
  • Hi,

    2325.  I have noticed that there are blank cells in the column my COUNTA formula is referring to.  I moved it to a different column that doesn't have any blanks and that works now.

    Thank you so much for your help.  Much appreciated.

    Monday, November 7, 2016 9:27 AM