How to input data and formulas on the same line of excel

Question

I have made a simple form to allow me to input data and store it on an excel table. Before adding simple formulas I was able to submit and the data and populate the next line of the excel table when I hit submit on my form.

I have now added simple formulas that will be used on the data I submit, however when I try to submit data, the formula and inputted data split over 2 separate line in my Excel table (as you can see in the attached image). Can you help me populate the data into the same line as the formulas.

Apologies for any bad code as I haven't tidied it yet, just getting the basics right.

Private Sub CommandButton1_Click()

    lastrow = ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row

    ThisWorkbook.Worksheets("Sheet1").Cells(lastrow + 1, 1).Value = TextBox1.Text    
    ThisWorkbook.Worksheets("Sheet1").Cells(lastrow + 1, 3).Value = TextBox9.Text    
    ThisWorkbook.Worksheets("Sheet1").Cells(lastrow + 1, 8).Value = TextBox12.Text    
    ThisWorkbook.Worksheets("Sheet1").Cells(lastrow + 1, 10).Value = TextBox11.Text    
    ThisWorkbook.Worksheets("Sheet1").Cells(lastrow + 1, 11).Value = TextBox10.Text    
    ThisWorkbook.Worksheets("Sheet1").Cells(lastrow + 1, 16).Value = TextBox15.Text    
    ThisWorkbook.Worksheets("Sheet1").Cells(lastrow + 1, 18).Value = TextBox14.Text
    ThisWorkbook.Worksheets("Sheet1").Cells(lastrow + 1, 19).Value = TextBox13.Text

    Sheets("Sheet1").Range("A4").Select
    ActiveCell.EntireRow.Insert shift:=xlDown
    Sheets("Sheet1").Range("A4:V4").Select
    Selection.Borders.Weight = xlThin    

    Sheets("Sheet1").Range("b4").Select
    ActiveCell.Formula = "=(a4*1440)"

    Sheets("Sheet1").Range("d4").Select
    ActiveCell.Formula = "=(b4*(c4/100))"

    Sheets("Sheet1").Range("e4").Select
    ActiveCell.Formula = "=((d4/b4)*100)"

    Sheets("Sheet1").Range("f4").Select
    ActiveCell.Formula = "=(100-e4)"

    Sheets("Sheet1").Range("i4").Select
    ActiveCell.Formula = "=(h4*1440)"

    Sheets("Sheet1").Range("l4").Select
    ActiveCell.Formula = "=(i4*(((j4+k4)/2)/100))"

    Sheets("Sheet1").Range("m4").Select
    ActiveCell.Formula = "=((l4/i4)*100)"

    Sheets("Sheet1").Range("n4").Select
    ActiveCell.Formula = "=(100-m4)"

    Sheets("Sheet1").Range("q4").Select
    ActiveCell.Formula = "=(p4*1440)"

    Sheets("Sheet1").Range("t4").Select
    ActiveCell.Formula = "=(q4*(((r4+s4)/2)/100))"

    Sheets("Sheet1").Range("u4").Select
    ActiveCell.Formula = "=((t4/q4)*100)"


    Sheets("Sheet1").Range("v4").Select
    ActiveCell.Formula = "=(100-u4)"

End Sub

As per your Screen Capture, when you add a new row, the formulas you are adding will work with blank data. Just add the formulas. — David García Bodego, Sep 09 '19 at 09:01

Pᴇʜ · Accepted Answer · 2019-09-09T09:26:02.140

Don't use .Select. You might benefit from reading How to avoid using Select in Excel VBA.
Use a variable InsertRow instead of LastRow and add + 1 there. So the calculation is done only once and not in every line you use it.
Use the InsertRow for your values and formulas, and adjust the formulas from row dependent formulas A4 to row independent A:A so the work in every row and not only row 4.
You should name your Button and TextBoxes properly (meaningful). Numbering them is the worst solution as you can easily mix them up and end up in a mess of controls.

So you end up with:

Option Explicit

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    Dim InsertRow As Long
    InsertRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row + 1

    With ws
        .Cells(InsertRow, 1).Value = TextBox1.Text
        .Cells(InsertRow, 3).Value = TextBox9.Text
        .Cells(InsertRow, 8).Value = TextBox12.Text
        .Cells(InsertRow, 10).Value = TextBox11.Text
        .Cells(InsertRow, 11).Value = TextBox10.Text
        .Cells(InsertRow, 16).Value = TextBox15.Text
        .Cells(InsertRow, 18).Value = TextBox14.Text
        .Cells(InsertRow, 19).Value = TextBox13.Text

        .Cells(InsertRow, "B").Formula = "=(A:A*1440)"
        .Cells(InsertRow, "D").Formula = "=(B:B*(C:C/100))"
        .Cells(InsertRow, "E").Formula = "=((D:D/B:B)*100)"
        .Cells(InsertRow, "F").Formula = "=(100-E:E)"
        .Cells(InsertRow, "I").Formula = "=(H:H*1440)"
        .Cells(InsertRow, "L").Formula = "=(I:I*(((J:J+K:K)/2)/100))"
        .Cells(InsertRow, "M").Formula = "=((L:L/I:I)*100)"
        .Cells(InsertRow, "N").Formula = "=(100-M:M)"
        .Cells(InsertRow, "Q").Formula = "=(P:P*1440)"
        .Cells(InsertRow, "T").Formula = "=(Q:Q*(((R:R+S:S)/2)/100))"
        .Cells(InsertRow, "U").Formula = "=((T:T/Q:Q)*100)"
        .Cells(InsertRow, "V").Formula = "=(100-U:U)"
    End With

    ' not sure if this is needed
    '    ws.Cells(InsertRow, "A").EntireRow.Insert shift:=xlDown
    ws.Range("A" & InsertRow & ":V" & InsertRow).Borders.Weight = xlThin
End Sub

Thanks for your help and tips. The excel now works as I intended. I will avoid using the .Select from now on and I will add more meaningful names. — Ryan McComb, Sep 09 '19 at 10:05

score 0 · Answer 2 · answered Sep 09 '19 at 09:01

0

Sheets("Sheet1").Range("A4").Select ActiveCell.EntireRow.Insert shift:=xlDown

The code above might move down your data. Why not you create the formula directly at the excel sheet?

answered Sep 09 '19 at 09:01

Lee Li Fong

274
1
6

Hi Lee, I got it working. I did try adding the formula directly to the Excel, however the code recognised there was formulas in the row and placed my inputted data in the row below. – Ryan McComb Sep 09 '19 at 10:08

How to input data and formulas on the same line of excel

2 Answers2