vba and worksheet formulas

[ajm]

I am having trouble adding a formula into a cell using the following macro:

Private Sub OptAll_Click()

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With

    Set FRPage = ThisWorkbook.Worksheets("fy10 v fy11 Revenue TREF Melb")
    LastRow = Range("A" & Rows.Count).End(xlUp).Row

    With FRPage
        .Range("e5").Formula = "=+IFERROR(VLOOKUP($A5,'FY11 QME'!$A$7:$G$346,6,0),"")"
        .Range("f5").Formula = "=IF(COUNTIF('FY11 QME'!$A$7:$A$346,$A5),IF(VLOOKUP($A5,'FY11 QME'!$A$7:$G$346,7,0)" _
                             & "="","",VLOOKUP($A5,'FY11 QME'!$A$7:$G$346,7,0)),"")"
        .Range("g5").Formula = "=+IFERROR(e5/f5)"

        .Range("q5").Formula = "=+IFERROR(VLOOKUP($A5,'FY12QME'!$A$7:$G$346,6,0),"")"
        .Range("r5").Formula = "=IF(COUNTIF('FY12QME'!$A$7:$A$346,$A5),IF(VLOOKUP($A5,'FY12QME'!$A$7:$G$346,7,0)" _
                             & "="","",VLOOKUP($A5,'FY12QME'!$A$7:$G$346,7,0)),"")"
        .Range("s5").Formula = "=+IFERROR(q5/r5)"

        .Calculate
    End With

    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True

    End With

End Sub

i keep getting application defined or object defined error.

 

[Rick Rothstein]

.Range("e5").Formula = "=+IFERROR(VLOOKUP($A5,'FY11 QME'!$A$7:$G$346,6,0),"")"

I'll use the above formula as an example... every place within your outer quotes, you used only two quote marks to indicate the empty string... inside outer quotes, each quote mark needs to be doubled up. The above formula should look like this...

.Range("e5").Formula = "=+IFERROR(VLOOKUP($A5,'FY11 QME'!$A$7:$G$346,6,0),"""")"

I highlighted the needed extra quote marks in red for you to be able to see easier. Remember, you have to do this for every internal quote mark.

By the way, you do not need that plus sign in front of the IF function call.

 

[repairman615]

Hello,

I am thinking it is the two lines with the line continuation:

Try like:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Example()<br><br>Range("A1").Formula = "=IF(COUNTIF('FY11 QME'!$A$7:$A$346,$A5),IF(VLOOKUP($A5,'FY11 QME'!$A$7:$G$346,7,0)" & _<br>        ","""",VLOOKUP($A5,'FY11 QME'!$A$7:$G$346,7,0)),"""")"<br>       <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[ajm]

thank you gentlemen, it was the double quotes, or lack of them. also missed the second half of my iferror formulas (third line in each section). methinks somebody has switched the decaf and regular jars again...

thanks again.