Extra character (') in dynamically generated formula

[jawbonium]

I am baffled. I am writing a macro to write the same forumla in the same cell in every sheet in my workbook except Summary (since the formula references the Summary sheet). Here is the script:

For Each ws In ActiveWorkbook.Worksheets
If (Not (ws.name = "Summary&quot) Then
ws.Cells(firstEmptyRow, 1).FormulaR1C1 = "=IF(Summary!A" _
& CStr(firstEmptyRow) _
& " > 0,Summary!A" _
& CStr(firstEmptyRow) _
& "," _
& Chr(34) _
& Chr(34) _
& &quot"
End If
Next

But here is what is getting written:

=IF(Summary!'A177' > 0,Summary!'A177',"&quot

The four extra apostrophes are rendering the formula invalid. Any thoughts on how to be rid of them?

 

[sfvb]

You're not using any R1C1 references, so just change FormulaR1C1 to Formula

For Each ws In ActiveWorkbook.Worksheets
If (Not (ws.name = "Summary&quot) Then
ws.Cells(firstEmptyRow, 1).Formula = "=IF(Summary!A" _
& CStr(firstEmptyRow) _
& " > 0,Summary!A" _
& CStr(firstEmptyRow) _
& "," _
& Chr(34) _
& Chr(34) _
& &quot"
End If
Next

 

[jawbonium]

That worked! Thank you very much!