help with end with end if statements

[jordanburch]

Sub COSARimportfinal21currentmonth()
Dim MyFile As String
Dim erow As Long
Dim Filepath As String
Dim wb1 As Workbook, wb2 As Workbook

' Dim ThisWorkbook.Worksheets("Variables").Range("A1").Value As String
' Dim ThisWorkbook.Worksheets("Variables").Range("A3").Value As String
' Dim ThisWorkbook.Worksheets("Variables").Range("A4").Value As String
' Dim ThisWorkbook.Worksheets("Variables").Range("A6").Value As String
' Dim ThisWorkbook.Worksheets("Variables").Range("A3").Value As String

Dim fn As String
Dim fn2 As String
Dim fn3 As String
Dim fn4 As String
Dim ShtName1 As String
Dim ShtName2 As String
Dim ShtName3 As String
ShtName1 = "Detail Lines"
ShtName2 = "Detail"
ShtName3 = "Detail -"

' ThisWorkbook.Worksheets("Variables").Range("A6").Value = InputBox("Enter month Name I.E. YYYYMM:", Default:="202005")
'ThisWorkbook.Worksheets("Variables").Range("A1").Value = InputBox("Enter month I.E. 08-MAY20", Default:="08-MAY20")
'ThisWorkbook.Worksheets("Variables").Range("A3").Value = Right(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 5)
' ThisWorkbook.Worksheets("Variables").Range("A3").Value = "FY" & Right(ThisWorkbook.Worksheets("Variables").Range("A3").Value, 2)
'ThisWorkbook.Worksheets("Variables").Range("A4").Value = ThisWorkbook.Worksheets("Variables").Range("A3").Value

fn = Left(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 6)
fn2 = Right(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 2)
fn3 = Right(ThisWorkbook.Worksheets("Variables").Range("A6").Value, 2)


Application.ScreenUpdating = False
Worksheets.Add(After:=Worksheets(1)).Name = "CO SAR"
Set wb1 = ThisWorkbook

fn4 = Right(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 5)
Filepath = "K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\Source Files\21 Field Details\" & ThisWorkbook.Worksheets("Variables").Range("A4").Value & "\" & ThisWorkbook.Worksheets("Variables").Range("A1").Value & "\Field Detail Lines\"
'K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\DRP\2020 DRP\2020-05 Reporting Cycle
MyFile = "CO21army" & fn4 & ".xlsx"

Dim strFileName As String
Dim strFileExists As String

strFileName = Filepath & MyFile
strFileExists = Dir(strFileName)

If strFileExists = "" Then
MsgBox "The current month 21 CO SAR file does not exist"

Else
erow = wb1.Sheets("CO SAR").Cells(Rows.Count, 14).End(xlUp).Offset(1, 0).Row
Set wb2 = Workbooks.Open(Filepath & MyFile)
With wb2


Dim ShtName As String
ShtName = "Sheet 1"
If Evaluate("isref('" & ShtName & "'!A1)") Then
'sheet exists do something
Else
'sheet doesn't exist do something else

If Evaluate("isref('" & ShtName1 & "'!A1)") Then
wb2.Sheets("Detail Lines").Range("q2:q1000").Value = MyFile
.Sheets("Detail Lines").Range("c2:q1000").Copy Destination:=wb1.Worksheets("CO SAR").Cells(erow, 1)

.Close savechanges:=False
ElseIf Evaluate("isref('" & ShtName3 & "'!A1)") Then
.Sheets("Detail Lines").Range("c21000").Copy Destination:=wb1.Worksheets("CO SAR").Cells(erow, 1)
.Close savechanges:=False

ElseIf Evaluate("isref('" & ShtName2 & "'!A1)") Then
.Sheets("Detail Lines").Range("c21000").Copy Destination:=wb1.Worksheets("CO SAR").Cells(erow, 1)
.Close savechanges:=False

End If


End With
End If

Application.ScreenUpdating = True
End Sub


compile error

end with without if

is the error

 

[Joe4]

The order of your command appears to be a bit messed up. Note the following things:
1. Every "IF..." that does not have the complete THEN clause on the same line of code requires an "END IF"
2. Every "WITH" requires an "END WITH"
3. The order of the "END..." statements MUST be in the exact opposite order that they were started in. So the last one created MUST be the first one closed.

So here is an example with 2 IFs and one with. I am using color coding and indenting to show the required order.

If ... Then
   If ... Then
        With ...
            ...
        End With
    End If
End If

I highly recommend that you indent your code in this fashion, so it is easier to see what is going on.
And when you post your code to this forum, please use code tags to maintain the formatting so we can see it too!

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

 

[jordanburch]

The order of your command appears to be a bit messed up. Note the following things:
1. Every "IF..." that does not have the complete THEN clause on the same line of code requires an "END IF"
2. Every "WITH" requires an "END WITH"
3. The order of the "END..." statements MUST be in the exact opposite order that they were started in. So the last one created MUST be the first one closed.

So here is an example with 2 IFs and one with. I am using color coding and indenting to show the required order.

If ... Then
   If ... Then
        With ...
            ...
        End With
    End If
End If

I highly recommend that you indent your code in this fashion, so it is easier to see what is going on.
And when you post your code to this forum, please use code tags to maintain the formatting so we can see it too!

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

thanks Joe! I got it. I appreciate your explanation as well its great to learn like that.

Jordan

 

[Joe4]

You are welcome.
Glad I was able to help!