Excel: Insert new row that contains excel formula in VBA[Updated]

Question

I have there the sample data of my project. As you can see there are 4 colums: deadline, submitted, description, & days_delayed. In addition, in the same sheet I have the insertion form of data. Where Range G1 is the deadline and the G3 is the actual date when the user submits its document.

In this code you'll the conditional statement written in formula way inside of vba. So that every time adds new row the formula will follows and copied to its corresponding cell range.

    Private Sub CommandButton1_Click()

    Dim Deadline, Submitted, Description, Days_Delayed As String
    Worksheets("Sheet3").Select
    Deadline = Range("G1")
    Submitted = Range("G3")
    Description = "=IF(AND(D2>0,ISBLANK(B2)),"NO- 
    DOCUMENT",IF(AND(D2<=0,NOT(ISBLANK(B2))),"ON-TIME", IF(AND(D2 > 0,NOT(ISBLANK(B2))),"DELAYED")))"
    Days_Delayed = "=IF(COUNT(A2:B2)=2,B2-A2,IF(B2="","0"))"


    Worksheets("Sheet3").Select
    Worksheets("Sheet3").Range("A2").Select
    If Worksheets("Sheet3").Range("A2").Offset(1, 0) <> "" Then
    Worksheets("Sheet3").Range("A2").End(xlDown).Select
    End If



    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = Deadline
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = Submitted
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Formula = Description
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Formula = Days_Delayed

    End Sub

This code is working fine but when I tried to implement the Formula inside of the vba codes. The error say "End Of Statement"

Fixed By Peh The Insertion of formula to VBA should be like this

Days_Delayed = "=IF(COUNT(N36:O36)=2,O36-N36,IF(O36="""",TODAY()-N36))"

Another Question is:

Since the add new row is a new data and the formula should not placed a defined range inside of it.

Days_Delayed = "=IF(COUNT(N36:O36)=2,O36-N36,IF(O36="""",TODAY()-N36))"

In this formula you can see that the formula ranges are defined. What I'm trying to fix is to do something like this:

Days_Delayed = "=IF(COUNT(N(i):O(i))=2,O(i)-N(i),IF(O(i)="""",TODAY()-(i)))"

You can see that there is variable i so when the new row comes in. The formula will automatically knows what cells needed to be calculated. I guess, looping is the appropriate way for this. Because If I remain the same formula with defined ranges. The output will be same. Is that possible?

Please note that `Dim Deadline, Submitted, Description, Days_Delayed As String` only declares the last variable as `String` all the others are `Variant`. You must specify a type for **every** variable: `Dim Deadline As String, Submitted As String, Description As String, Days_Delayed As String`. • And please read and apply to your code [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Using `Select` slows down your code a lot and is not very reliable. — Pᴇʜ, Jan 10 '19 at 08:13
To your Issue: If you use quotes `"` within other quotes you must double them. E.g: `Days_Delayed = "=IF(COUNT(A2:B2)=2,B2-A2,IF(B2="","0"))"` must be `Days_Delayed = "=IF(COUNT(A2:B2)=2,B2-A2,IF(B2="""",""0""))"` — Pᴇʜ, Jan 10 '19 at 08:14
Now I see, Is there a way for not declaring the cell range inside of that formula. Like loop? — Pablo, Jan 10 '19 at 08:54
I don't understand what you mean. Please [edit] your question update the code with the fixes I mentioned above and ask a more detailed question. Telling us what you *want* to do not what you *not want* to do is probably easier to understand. — Pᴇʜ, Jan 10 '19 at 08:57

score 1 · Accepted Answer · answered Jan 10 '19 at 09:34

The main issue is that you use .Select. Instead determine the next free row number with

nRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1

and use it to access the cells directly ws.Cells(nRow, "A").Value.

Also don't declare your variables as String instead use Dim Deadline As Range otherwise your dates will be converted to strings and you cannot calculate anymore with the dates.

In the formulas the row numbers need to be substituted by " & nRow & " to insert the row number of the next free row instead of a fixed row number.

Option Explicit

Public Sub AddData()
    Dim ws As Worksheet 'define worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet3")

    Dim Deadline As Range 'define deadline range
    Set Deadline = ws.Range("G1")

    Dim Submitted As Range 'define submitted range
    Set Submitted = ws.Range("G3")

    Dim nRow As Long 'find next free row = last used used row +1
    nRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1


    ws.Cells(nRow, "A").Value = Deadline.Value
    ws.Cells(nRow, "B").Value = Submitted.Value

    ws.Cells(nRow, "C").Formula = "=IF(AND(D" & nRow & ">0,ISBLANK(B" & nRow & ")),""NO-DOCUMENT"",IF(AND(D" & nRow & "<=0,NOT(ISBLANK(B" & nRow & "))),""ON-TIME"", IF(AND(D" & nRow & " > 0,NOT(ISBLANK(B" & nRow & "))),""DELAYED"")))"
    ws.Cells(nRow, "D").Formula = "=IF(COUNT(A" & nRow & ":B" & nRow & ")=2,B" & nRow & "-A" & nRow & ",IF(B" & nRow & "="""",""0""))"
End Sub

Excel: Insert new row that contains excel formula in VBA[Updated]

1 Answers1