VBA Formula Setting in a for Loop - Syntax Issue-CodePudding

Hi Trying to get some help to see why this is not working in a Macro that I have setup. The area where the debugger causes an issue is at the 2nd Selection.Formula area.

Sub PrintAllonges()
'
' PrintAllonges Macro
'
' Keyboard Shortcut: Ctrl Shift Y
'

Dim pdfName As String, FullName As String, Path As String, lRow As Long


Set oFSO = CreateObject("Scripting.FileSystemObject")

Path = CreateObject("WScript.Shell").specialfolders("Desktop")

' Create Desktop Folder if not exists

If oFSO.FolderExists(Path & "\Allonges") Then
Else
    MkDir Path & "\Allonges"
End If
    
'Turn off Screen Update

Sheets("MissingAllonges").Select
lRow = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox (lRow)
Sheets("AllongeTemplate").Select

Application.ScreenUpdating = False


For i = 2 To lRow

    Range("G6").Select
    Selection.Formula = "=MissingAllonges!I" & i
    Range("E11").Select
    Selection.Formula = _
        "=TEXT(MONTH(MissingAllonges!D" & i & "),""mmmm"")&"" ""&DAY(MissingAllonges!D" & i & ")&"", ""&YEAR(MissingAllonges!D" & i & ")"""
         

pdfName = Sheets("AllongeTemplate").Range("H7").Value & " - " & Sheets("AllongeTemplate").Range("G6").Value & " Allonge"
FullName = Path & "\Allonges\" & pdfName & ".pdf"


ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FullName, OpenAfterPublish:=False

Next i


Application.ScreenUpdating = True


End Sub

I put this in and got to work for other formulas where I am updating the loop but I can't get this to work and getting an error on syntax.

CodePudding user response：

You have extra quotation marks at the end of the formula.

The corrected formula would be:

.Formula = "=TEXT(MONTH(MissingAllonges!D" & i & "),""mmmm"")&"" ""&DAY(MissingAllonges!D" & i & ")&"", ""&YEAR(MissingAllonges!D" & i & ")"

But I agree with @BigBen that the formula could be simplified, ie:

.Formula = "=TEXT(MissingAllonges!D" & i & ", ""mmmm d, yyyy"")"