help with end with end if statements

jordanburch

Active Member
Joined
Jun 10, 2016
Messages
309
Office Version
  1. 2016
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("c2:p1000").Copy Destination:=wb1.Worksheets("CO SAR").Cells(erow, 1)
.Close savechanges:=False

ElseIf Evaluate("isref('" & ShtName2 & "'!A1)") Then
.Sheets("Detail Lines").Range("c2:p1000").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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,949
Office Version
  1. 365
Platform
  1. Windows
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.

Rich (BB code):
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!
 
Solution

jordanburch

Active Member
Joined
Jun 10, 2016
Messages
309
Office Version
  1. 2016
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.

Rich (BB code):
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!
thanks Joe! I got it. I appreciate your explanation as well its great to learn like that.

Jordan
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,949
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad I was able to help!
:)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,751
Messages
5,638,154
Members
417,010
Latest member
jnuss03

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top