9

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
chaostheory
  • 1,621
  • 4
  • 20
  • 36
BellHopByDayAmetuerCoderByNigh
  • 1,107
  • 4
  • 21
  • 41
  • Why are you using `with` blocks if you are only taking advantage of the convenience for one property assignment? For example, you can write `Range("E2:E" & Cells(Rows.Count, "C").End(xlUp).Row).Formula = "=IF(C2 – JamesFaix Mar 23 '17 at 22:46

5 Answers5

12

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.

OpiesDad
  • 3,385
  • 2
  • 16
  • 31
  • Agreed. Using `Select` and `ActiveCell` is much slower and more ambiguous than just specifying sheet names and cell addresses. `ActiveCell`, `ActiveWorkbook` and so on also cease to have values when certain dialog boxes are open or in focus. – JamesFaix Mar 23 '17 at 22:48
3
  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
JamesFaix
  • 8,050
  • 9
  • 37
  • 73
  • 1
    This is a great answer. I was too lazy to spell all this out. The 'Rows.Count` combined with `.End(xlUp).Row` is a standard way to find the last row as I think `UsedRange` can have issues if you have deleted values from cells. I agree that it is much better to do this in a variable than in the middle of trying to set the formula, though. Everything else is spot on. – OpiesDad Mar 24 '17 at 13:13
0

As well as using double quotes you may need to use 0 in the first two formula otherwise they may evaluate to empty strings. This may give unexpected results for the last formula i.e. incorrectly return "Issue".

If you do not have blank columns between your data and the 3 new columns you can use CurrentRegion to determine the number of rows:

Range("E2:E" & Cells.CurrentRegion.Rows.Count).Formula = "=if(C2'<'B2,B2-C2,0)"

Range("F2:F" & Cells.CurrentRegion.Rows.Count).Formula = "=if(C2>B2,C2-B2,0)"

Range("G2:G" & Cells.CurrentRegion.Rows.Count).Formula = if(F2>0,""Issue"","""")"
MartinTeeVarga
  • 10,478
  • 12
  • 61
  • 98
Steve C
  • 11
  • None of the formulas reference column `E`, so I don't think that would cause issue. – JamesFaix Mar 24 '17 at 00:21
  • use it like this: ThisWorkbook.Sheets("Data").Range("C43").Formula2R1C1 = what ever your normal formula is just add double quotes to it everywhere , so if there is 1 quote then it turnes into 2, if 2 then equals 4 quotes and so on. then you are done. – DeerSpotter Jul 05 '22 at 21:01
0

  1. The first issue is the selecting of cells. This requires the macro to select the cell, then determine the cell address. If you need to actually select a cell, use Application.ScreenUpdating = False. Then the macro doesn't have to show the cursor selection of a cell. Dropping the select and incorporating the range into the formula assignment code line like below will gain some speed/efficiency.

    Range("E2").FormulaR1C1 = "Under"

  2. Range("E2:E" & Cells(Rows.Count, "C").End(xlUp).Row) is the code version of selecting the last cell in a blank column (row 1048576), then using the keystroke of ctrl and the up key to determine the lowest/last used cell. This gets you a row count of 1 every time since the column is blank. Since you're looking for the last row. It may be faster to count down from the top. My favorite method for this is a loop. Increment a variable within a loop, while looking for the last row. Then, the variable can be used instead of your bottom up strategy.

    t = 0
Do Until Range("C2").Offset(t, 0).Value = ""
t = t + 1
Loop

With Range("E2:E" & t)
.Formula = "=IF(C2<B2,B2-C2,"""")"
End With`

  3. Just like TSQL, quote characters need their own quote characters.

    .Formula = "=IF(C2<B2,B2-C2,"""")"
  4. The Range Fillup VBA function can be utilized in this case to fill all cells from the bottom with a common formula, accounting for Excel Formula Reference Relativity. The code below starts with the range that we got from the loop counter. Next, we set a variable equal to the total rows in Excel minus the row corresponding to the counter row. Then, we resize the original region by the necessary rows and use the FillDown function to copy the first formula down.

  5. Here's the resulting code. This will fill the range starting from the last row in Excel. 

     Sub Gre()
 Range("E2").FormulaR1C1 = "Under"
 Range("F2").FormulaR1C1 = "Over"
 Range("G2").FormulaR1C1 = "Result"

 Do While Range("e2").Offset(t, 0).Value <> ""
 t = t + 1
 Loop
 Range("E2").Offset(t, 0).Formula = "=IF(C2<B2,B2-C2,"""")"
 r1 = Range("e2").EntireColumn.Rows.Count
 r2 = Range("E2").Offset(t, 0).Row
 Range("E2").Offset(t, 0).Resize(r1 - r2, 1).FillDown
 Range("F2").Offset(t, 0).Formula = "=IF(C2>B2,C2-B2,0)"
 Range("F2").Offset(t, 0).Resize(r1 - r2, 1).FillDown
 Range("G2").Offset(t, 0).Formula = "=IF(F2>0,""Issue"","""")"
 Range("G2").Offset(t, 0).Resize(r1 - r2, 1).FillDown
 End Sub
Geoffrey Fuller
  • 186
  • 1
  • 6
0

Please try the following sample hope it will help you to wright formula in VBA

   Sub NewEntry()
    Dim last_row As Integer
    Dim sht1 As Worksheet
    Dim StockName As String

Set sht1 = Worksheets("FNO MW")
last_row = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'MsgBox last_row
StockName = sht1.Cells(last_row, 1).Value
sht1.Cells(last_row, 1).Formula = "=RTD(""pi.rtdserver"", ,"" " & StockName & " "", ""TradingSymbol"")"

End Sub

Sapnandu
  • 620
  • 7
  • 9