VBA To Add Formula To Cell [duplicate]

Question

I am attempting to write some VBA which will add header text to 3 cells then fill a formula all the way down to the last row. I have written the below, which writes the headers no problems, but when it get's to my first .Formula it throws a

Application Defined or Object Defined error

What needs to be altered so that this macro will execute successfully? (The formulas were pulled directly from the formula in the cell, so I know they are valid formulas at least on the "front-end")

Function Gre()
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "Under"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "Over"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "Result"

    With Range("E2:E" & Cells(Rows.Count, "C").End(xlUp).Row)
        .Formula = "=IF(C2<B2,B2-C2,"")"
    End With
    With Range("F2:F" & Cells(Rows.Count, "C").End(xlUp).Row)
        .Formula = "=IF(C2>B2,C2-B2,0)"
    End With
    With Range("G2:G" & Cells(Rows.Count, "C").End(xlUp).Row)
        .Formula = "=IF(F2>0,'Issue',"")"
    End With
End Function

Answer 1

The problem is likely that you are escaping the quotes with the formula.

What you need is:

.Formula = "=IF(C2>B2,B2-C2,"""")"

for the first one, for example. The other quotes need to be doubled as well.

As a side-note, it would also be best to specify the sheet you are working on with something like:

 Dim ws as worksheet
 Set ws = Sheets("mySheet")
 ws.Range("E2").FormulaR1C1 = "Under"

etc.

If you don't do this, you can sometimes have errors happen while running the code.

Answer 2

1. As suggested by OpiesDad, to minimize ambiguity, avoid ActiveCell and the like.
2. Using Select will also slow down performance a lot compared to assigning to cells directly.
3. I'm pretty sure you need to escape quotes in Excel formulas inside of VBA by doubling the quotes, so a normal empty string becomes """". You also have Issue in single quotes in a formula, which I'm pretty sure will error in Excel; that should be in escaped double quotes as well.
4. I'm having a hard time figuring out what Range("E2:E" & Cells(Rows.Count, "C").End(xlUp).Row) actually does, but it sounds like you want to select E2 to the last used row of the sheet. Avoid Rows.Count or just generally referring to the rows of a sheet, as that will go to row 10^31. Use Worksheet.UsedRange to get the range from the first row and column with content to the last row and column with content. This also includes empty strings and can be a bit tricky sometimes, but is usually better than dealing with thousands of extra rows.

Also,

1. You don't need to use With if your only enclosing one statement, although it won't cause any problems.

2. I would not mix use of Range.Formula and Range.FormulaR1C1 unless you have a reason to.

   Function Gre() 
   
       Dim ws as Worksheet
       Set ws = ActiveSheet
   
       Dim used as Range
       Set used = ws.UsedRange
   
       Dim lastRow as Integer
       lastRow = used.Row + used.Rows.Count - 1
   
       ws.Range("E2").Formula = "Under"
       ws.Range("F2").Formula = "Over"
       ws.Range("G2").Formula = "Result"
   
       ws.Range("E2:E" & lastRow).Formula = "IF(C2<B2, C2-B2, """")"
       ws.Range("F2:F" & lastRow).Formula = "IF(C2<B2, C2-B2, 0)"
       ws.Range("G2:G" & lastRow).Formula = "IF(F2>0, ""Issue"", """")"
   
   End Function