Autofill formula to next row

  • Hi and thank you in advance for taking time to try and help me. I have a user form to input data to a spreadsheet. The spreadsheet has 4 columns containing vlookups and formulas used to calculate a total. I would like the formulas to be copied to the new row each time a new record is added (button click event). Here's a screenshot to help explain:


    Columns 22 - 24 have vlookups with formulas and column 25 just totals the other three. I manually entered the formulas into the first row and after adding the records using the form dragged them down to the rows below. The formulas work, but I would like to have them added automatically each time I add a record using the form (button click event). Notice the last record doesn't have any values. I added the record but did not drag the formulas down. Please let me know if you need more information, need to see vb, etc.

    Thanks again!

  • Re: Autofill formula to next row

    I found this, and it seems to work:

    'Copy Formulas from V, W, X, and Y to new row
        With Sheets("Inv History")
            .Range("V5:V" & .Cells(.Rows.Count, "A").End(xlUp).Row).Formula = "=VLOOKUP($F5,'Cust Rates'!$A$4:D999999,2,FALSE)*O5"
            .Range("W5:W" & .Cells(.Rows.Count, "A").End(xlUp).Row).Formula = "=VLOOKUP($F5,'Cust Rates'!$A$4:E999999,3,FALSE)*N5"
            .Range("X5:X" & .Cells(.Rows.Count, "A").End(xlUp).Row).Formula = "=VLOOKUP($F5,'Cust Rates'!$A$4:F999999,4,FALSE)*(N5+O5)"
            .Range("Y5:Y" & .Cells(.Rows.Count, "A").End(xlUp).Row).Formula = "=SUM(V5:X5)"
        End With

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!