COMPILE ERROR: End With without With

[JMAC1971]

I am getting an error in this Script, but I'm not sure why... I have two With Statements and both have an EndWith. Can you please assist with what I'm missing?

I'm using the With statements to define two sheets in my File (FORM_INPUT) & (JOB_LIST)


Sub CreatePDFForms()
Dim PDFTemplateFile, NewPDFName, SavePDFFolder, DOJM As String
Dim SERVDATE
Dim DOJMRow, LastRow As Long
With Worksheets("FORM_INPUT")
PDFTemplateFile = .Range("A47").Value 'Location of Template file in spreadsheet
SavePDFFolder = .Range("E22").Value 'Saved PDF Folder

Const SW_SHOWMAXIMIZED = 3
Dim wShell As Object 'Shell32.Shell

Set wShell = CreateObject("Shell.Application")
wShell.ShellExecute "O:\Division III\Decatur Engineering\Jeff Work\Automated Job Forms\Master FORM 8-22.PDF", , , "open", SW_SHOWMAXIMIZED

Application.Wait Now + 0.00006

End With

With Worksheets("JOB_LIST")

LastRow = .Range("A9999").End(xlUp).Row

For DOJMRow = LastRow To LastRow

DOJM = .Range("A" & LastRow).Value 'DOJM
SERVTPYE = .Range("J" & LastRow).Value 'Service Type
Application.SendKeys "{Tab}", True
Application.SendKeys "{Tab}", True
Application.SendKeys "{Tab}", True
Application.SendKeys "{Tab}", True

Application.SendKeys DOJM, True 'Enter DOJM# in PDF File
Application.Wait Now + 0.00001

Application.SendKeys "{Tab}", True
Application.SendKeys .Range("C" & DOJMRow), True 'Enter Date in PDF File
Application.Wait Now + 0.00001

Application.SendKeys "{Tab}", True
Application.SendKeys "{Tab}", True
Application.SendKeys .Range("F" & DOJMRow), True 'Enter Address in PDF File
Application.Wait Now + 0.00001

Application.SendKeys "{Tab}", True
Application.SendKeys "{Tab}", True
Application.SendKeys .Range("H" & DOJMRow), True 'Enter County in PDF File
Application.Wait Now + 0.00001

Application.SendKeys "{Tab}", True
Application.SendKeys .Range("G" & DOJMRow), True 'Enter City in PDF File
Application.Wait Now + 0.00001

Application.SendKeys "{Tab}", True
Application.SendKeys .Range("I" & DOJMRow), True 'Enter Township in PDF File
Application.Wait Now + 0.00001

Application.SendKeys "{Tab}", True
Application.SendKeys "{Tab}", True
Application.SendKeys "{Tab}", True
Application.SendKeys .Range("L" & DOJMRow), True 'Enter Circuit# in PDF File
Application.Wait Now + 0.00001

Application.SendKeys "{Tab}", True
Application.SendKeys .Range("M" & DOJMRow), True 'Enter Circuit Voltage in PDF File
Application.Wait Now + 0.00001

Application.SendKeys "+^(s)", True
Application.Wait Now + 0.00002
Application.SendKeys "%", True
Application.Wait Now + 0.00001
Application.SendKeys SavePDFFolder & "\" & "IPCN" & DOJM & SERVTYPE & ".pdf"
Application.Wait Now + 0.00002
Application.SendKeys "%(s)", True
Application.Wait Now + 0.00002

Application.SendKeys "^(q)", True
Application.SendKeys "{numlock}%s", True

End With
End Sub

 

[HaHoBe]

Hi,

you should add

    Next
  End With
End Sub

Ciao,
Holger

 

[JMAC1971]

One more request... I need to format the Date entry as dd/mm/yyy but I'm getting an error. Any thoughts?

Application.SendKeys .Range("C" & DOJMRow).Format(Date, "dd/mm/yyyy"), True 'Enter Date in PDF File

 

[HaHoBe]

Hi JMAC1971,

maybe try

Application.SendKeys .Range("C" & DOJMRow) & Format(Date, "dd/mm/yyyy"), True 'Enter Date in PDF File

Ciao,
Holger

 

[JMAC1971]

OK the only problem I'm running into is it's not carrying my date over correctly. (((.Range("C" & DOJMRow)))) - Contains a date of when a job was entered, and when I run this script it appears to be putting two dates in my pdf Form, which then gives me a validation error there since everything should be in mm/dd/yy format. Any ideas.

Application.SendKeys .Range("C" & DOJMRow) & Format(Date, "mm/dd/yy"), True 'Enter Date in PDF File

PS. Yes I did change the order of Days & Months from previous post -

 

[HaHoBe]

Hi JMAC1971,

if a date is located in Column C you should try

Application.SendKeys Format(.Range("C" & DOJMRow), "mm/dd/yy"), True 'Enter Date in PDF File

Ciao,
Holger