Understanding IF - THEN status

rjplante

rjplante

Well-known Member
Joined
Oct 31, 2008
Messages
574
Office Version
  1. 365
Platform
  1. Windows
I have a long macro which I have summarized below and I am trying to understand why the 'Compile VBAProject' tool flags the 'END IF' statements near then end of my macro code. From my understanding, you need to close your 'IF' statements with and 'END IF' statement. I have written this summary so that I could track my IF/THEN/END IF statements in my macro. I would appreciate it if anyone could look this over and tell me why the last two 'END IF' statements with an '*' in front of them appear to be unnecessary, as they lead to flags when I run the Compile VBAProject tool.

I am a novice with the VBA programming and I am trying to learn as fast as I can. I appreciate all the support found here.

Thanks to all of you power programmers,

Robert

=================================

SUB CONICAL_UPDATE

IF case #1 (open IF #1)

Code section - MsgBox ‘YES – NO’

IF answer is ‘NO’ (open if #2)

Code section - Amend data table

IF data is from Form #1 – case #1 (open IF #3)

Code section

ELSE IF data is from Form #2 – case #2 (continuation of IF #3)

Code section

EXIT SUB
END IF data from case 1 & 2 has been dealt with (close IF#3)

ELSE IF answer is ‘YES’ (continuation of IF #2)

Code section – Transfer and overwrite data table

 IF replacement data comes from Form #1 (open IF #4)

Code section

ELSE IF replacement data comes from Form #2 (Continuation of IF#4)

Code section

*END IF replacement data dealt with (Close of IF #4)

**END IF ‘YES’ and ‘NO’ cases dealt with (close of IF #2)

ELSE what to do if case#1 is FALSE (continuation of IF #1)

Code section

END IF ‘TRUE’ and ‘FALSE’ cases dealt with (close of IF #1)

END SUB

*When just this ‘End IF’ statement is here results in a “Compile error: ELSE without IF” statement.
**When both ‘END IF’ statements are here this results in a “Compile error: End IF without block IF” statement.

Removal of both of the above ‘END IF’ statements results in no compile errors when running the code “Compile VBA Project” tool.


=================================
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
Here's my actual code for this macro.

Code: 
Sub Update_Conical_Trendline()

[COLOR=SeaGreen]'   Check to see if the data has been updated.[/COLOR]
If Range("Z15").Value <> "" Then

i = MsgBox("Trendline data has already been transferred. " & vbCrLf & "Click ""YES"" to overwrite existing data with current data, " & vbCrLf & "click ""NO"" to manually update a couple of values.", vbYesNo + vbExclamation + vbDefaultButton2)

If i = 7 Then 'NO

Application.ScreenUpdating = False
File1 = ActiveWorkbook.Name

[COLOR=SeaGreen]' UPDATE CONICAL-1[/COLOR]

If Range("Z18").Value = 1 Then

ChDir "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB QC\510k Lot Testing\PCB QC Trendline Data"
Workbooks.Open Filename:= _
    "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB QC\510k Lot Testing\PCB QC Trendline Data\PCB Conicals Trendline.xlsm"

ActiveSheet.Unprotect Password:="bioe1025"

Range("KA1").ClearContents

Range("KA3").ClearContents
Range("KA3").Value = 1

Workbooks(File1).Activate

Sheets("QC5003.4A-1 FINAL QC CONICAL").Visible = True
Sheets("QC5003.4A-1 FINAL QC CONICAL").Range("Z15").Copy

Application.ScreenUpdating = True
Workbooks("PCB Conicals Trendline.xlsm").Activate
Application.ScreenUpdating = False

   Dim Found As Range
    Dim ws As Worksheet
    
    Set ws = Sheets("Conicals")
    
    ws.Range("KA1").PasteSpecial Paste:=xlPasteValues
[COLOR=SeaGreen]
'   find row[/COLOR]
     Set Found = ws.Range("A:A").Find(What:=Range("KA1").Value, _
                                      LookIn:=xlValues, _
                                      LookAt:=xlWhole, _
                                      SearchOrder:=xlNext, _
                                      MatchCase:=False)
    
    If Not Found Is Nothing Then
        With Found.EntireRow
            .Interior.ColorIndex = 4
            ws.Cells.Locked = True      [COLOR=SeaGreen]'Lock all cells[/COLOR]
            .Locked = False             [COLOR=SeaGreen]'Unlock just Found row[/COLOR]
        End With
    Else
        MsgBox "Couldn't match " & Range("KA1").Value & " in column A."
    End If

ActiveSheet.Protect Password:="bioe1025"
ActiveSheet.EnableSelection = xlUnlockedCells

Application.ScreenUpdating = True
Exit Sub
[COLOR=SeaGreen]
' UPDATE CONICAL-2 DATA[/COLOR]

ElseIf Range("Z18").Value = 2 Then

ChDir "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB QC\510k Lot Testing\PCB QC Trendline Data"
Workbooks.Open Filename:= _
    "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB QC\510k Lot Testing\PCB QC Trendline Data\PCB Conicals Trendline.xlsm"

ActiveSheet.Unprotect Password:="bioe1025"

Range("KA1").ClearContents

Range("KA3").ClearContents
Range("KA3").Value = 2

Workbooks(File1).Activate

Sheets("QC5003.4A-2 FINAL QC CONICAL").Visible = True
Sheets("QC5003.4A-2 FINAL QC CONICAL").Range("Z15").Copy

Application.ScreenUpdating = True
Workbooks("PCB Conicals Trendline.xlsm").Activate
Application.ScreenUpdating = False

[COLOR=SeaGreen]'   Dim Found As Range
'   Dim ws As Worksheet[/COLOR]
    
    Set ws = Sheets("Conicals")
    
    ws.Range("KA1").PasteSpecial Paste:=xlPasteValues

[COLOR=SeaGreen]'   find row[/COLOR]
     Set Found = ws.Range("A:A").Find(What:=Range("KA1").Value, _
                                      LookIn:=xlValues, _
                                      LookAt:=xlWhole, _
                                      SearchOrder:=xlNext, _
                                      MatchCase:=False)
    
    If Not Found Is Nothing Then
        With Found.EntireRow
            .Interior.ColorIndex = 4
            ws.Cells.Locked = True      'Lock all cells
            .Locked = False             'Unlock just Found row
        End With
    Else
        MsgBox "Couldn't match " & Range("KA1").Value & " in column A."
    End If

ActiveSheet.Protect Password:="bioe1025"
ActiveSheet.EnableSelection = xlUnlockedCells

Application.ScreenUpdating = True
Exit Sub

End If

[COLOR=SeaGreen]'===============================================================================
[/COLOR]

ElseIf i = 6 Then 'YES
[COLOR=SeaGreen]
' TRANSFER CONICAL-1 DATA AND OVERWRITE EXISTING DATA[/COLOR]

If Range("Z18").Value = 1 Then

File1 = ActiveWorkbook.Name

Application.ScreenUpdating = False

[COLOR=SeaGreen]'open QC Data trendline workbook[/COLOR]

ChDir "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB QC\510k Lot Testing\PCB QC Trendline Data"
Workbooks.Open Filename:= _
    "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB QC\510k Lot Testing\PCB QC Trendline Data\PCB Conicals Trendline.xlsm"

ActiveSheet.Unprotect Password:="bioe1025"
Range("KA1").ClearContents

Range("KA3").ClearContents
Range("KA3").Value = 1

Workbooks(File1).Activate

Sheets("QC5003.4A-1 FINAL QC CONICAL").Visible = True
Sheets("QC5003.4A-1 FINAL QC CONICAL").Range("Z15").Copy
Workbooks("PCB Conicals Trendline.xlsm").Activate
Sheets("Conicals").Range("KA1").PasteSpecial Paste:=xlPasteValues


Workbooks(File1).Activate
[COLOR=SeaGreen]' copy data[/COLOR]
Sheets("Conical Data Transfer").Visible = True
Sheets("Conical Data Transfer").Range("A2:JQ2").Copy

Workbooks("PCB Conicals Trendline.xlsm").Activate

[COLOR=SeaGreen]'Loop to find row[/COLOR]
Range("A2").Select

Do

If ActiveCell.Value <> Range("KA1").Value Then
Selection.Offset(1, 0).Select
End If

Loop Until ActiveCell.Value = Range("KA1").Value

ActiveCell.Offset(0, 1).Select
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False

End If

ActiveSheet.Protect Password:="bioe1025"

ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks(File1).Activate

Sheets("QC5003.4A-1 FINAL QC CONICAL").Range("R35").Value = "Updated" & "-" & Date
Call Intro_page

MsgBox "Trendline Data has been updated."

Application.ScreenUpdating = True
[COLOR=SeaGreen]
'-------------------------------------------------------------------------------

'UPDATE CONICAL-2 DATA[/COLOR]

ElseIf Range("Z18").Value = 2 Then

File1 = ActiveWorkbook.Name

Application.ScreenUpdating = False

[COLOR=SeaGreen]'open QC Data trendline workbook[/COLOR]

ChDir "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB QC\510k Lot Testing\PCB QC Trendline Data"
Workbooks.Open Filename:= _
    "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB QC\510k Lot Testing\PCB QC Trendline Data\PCB Conicals Trendline.xlsm"

ActiveSheet.Unprotect Password:="bioe1025"
Range("KA1").ClearContents

Range("KA3").ClearContents
Range("KA3").Value = 2

Workbooks(File1).Activate

Sheets("QC5003.4A-2 FINAL QC CONICAL").Visible = True
Sheets("QC5003.4A-2 FINAL QC CONICAL").Range("Z15").Copy
Workbooks("PCB Conicals Trendline.xlsm").Activate
Sheets("Conicals").Range("KA1").PasteSpecial Paste:=xlPasteValues


Workbooks(File1).Activate
[COLOR=SeaGreen]' copy data[/COLOR]
Sheets("Conical Data Transfer").Visible = True
Sheets("Conical Data Transfer").Range("A4:JQ4").Copy

Workbooks("PCB Conicals Trendline.xlsm").Activate

'Loop to find row
Range("A2").Select

Do

If ActiveCell.Value <> Range("KA1").Value Then
Selection.Offset(1, 0).Select
End If

Loop Until ActiveCell.Value = Range("KA1").Value

ActiveCell.Offset(0, 1).Select
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False

End If

ActiveSheet.Protect Password:="bioe1025"

ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks(File1).Activate

Sheets("QC5003.4A-2 FINAL QC CONICAL").Range("R35").Value = "Updated" & "-" & Date
Call Intro_page

MsgBox "Trendline Data has been updated."

Application.ScreenUpdating = True
[COLOR=SeaGreen]
'End If
'End If[/COLOR]

Else
Call Trendline_update_Conicals
End If

End Sub
 
Upvote 0
Upvote 0
You had already closed your IFs. Your summary layout is wrong. See this indented version to see where all IF and matching ELSEIF/ELSE/END IFs are:
Code: 
Sub Update_Conical_Trendline()

'   Check to see if the data has been updated.
    If Range("Z15").Value <> "" Then
    
        i = MsgBox("Trendline data has already been transferred. " & vbCrLf & "Click ""YES"" to overwrite existing data with current data, " & vbCrLf & "click ""NO"" to manually update a couple of values.", vbYesNo + vbExclamation + vbDefaultButton2)
    
        If i = 7 Then 'NO
        
            Application.ScreenUpdating = False
            File1 = ActiveWorkbook.Name
            
            ' UPDATE CONICAL-1
            
            If Range("Z18").Value = 1 Then
            
                ChDir "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB QC\510k Lot Testing\PCB QC Trendline Data"
                Workbooks.Open Filename:= _
                    "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB QC\510k Lot Testing\PCB QC Trendline Data\PCB Conicals Trendline.xlsm"
                
                ActiveSheet.Unprotect Password:="bioe1025"
                
                Range("KA1").ClearContents
                
                Range("KA3").ClearContents
                Range("KA3").Value = 1
                
                Workbooks(File1).Activate
                
                Sheets("QC5003.4A-1 FINAL QC CONICAL").Visible = True
                Sheets("QC5003.4A-1 FINAL QC CONICAL").Range("Z15").Copy
                
                Application.ScreenUpdating = True
                Workbooks("PCB Conicals Trendline.xlsm").Activate
                Application.ScreenUpdating = False
            
                Dim Found As Range
                Dim ws As Worksheet
                
                Set ws = Sheets("Conicals")
                
                ws.Range("KA1").PasteSpecial Paste:=xlPasteValues
            
            '   find row
                 Set Found = ws.Range("A:A").Find(What:=Range("KA1").Value, _
                                                  LookIn:=xlValues, _
                                                  LookAt:=xlWhole, _
                                                  SearchOrder:=xlNext, _
                                                  MatchCase:=False)
                
                If Not Found Is Nothing Then
                    With Found.EntireRow
                        .Interior.ColorIndex = 4
                        ws.Cells.Locked = True      'Lock all cells
                        .Locked = False             'Unlock just Found row
                    End With
                Else
                    MsgBox "Couldn't match " & Range("KA1").Value & " in column A."
                End If
        
                ActiveSheet.Protect Password:="bioe1025"
                ActiveSheet.EnableSelection = xlUnlockedCells
                
                Application.ScreenUpdating = True
                Exit Sub
    
    ' UPDATE CONICAL-2 DATA
    
            ElseIf Range("Z18").Value = 2 Then
    
                ChDir "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB QC\510k Lot Testing\PCB QC Trendline Data"
                Workbooks.Open Filename:= _
                    "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB QC\510k Lot Testing\PCB QC Trendline Data\PCB Conicals Trendline.xlsm"
                
                ActiveSheet.Unprotect Password:="bioe1025"
                
                Range("KA1").ClearContents
                
                Range("KA3").ClearContents
                Range("KA3").Value = 2
                
                Workbooks(File1).Activate
                
                Sheets("QC5003.4A-2 FINAL QC CONICAL").Visible = True
                Sheets("QC5003.4A-2 FINAL QC CONICAL").Range("Z15").Copy
                
                Application.ScreenUpdating = True
                Workbooks("PCB Conicals Trendline.xlsm").Activate
                Application.ScreenUpdating = False
            
            '   Dim Found As Range
            '   Dim ws As Worksheet
                
                Set ws = Sheets("Conicals")
                
                ws.Range("KA1").PasteSpecial Paste:=xlPasteValues
            
            '   find row
                 Set Found = ws.Range("A:A").Find(What:=Range("KA1").Value, _
                                                  LookIn:=xlValues, _
                                                  LookAt:=xlWhole, _
                                                  SearchOrder:=xlNext, _
                                                  MatchCase:=False)
                
                If Not Found Is Nothing Then
                    With Found.EntireRow
                        .Interior.ColorIndex = 4
                        ws.Cells.Locked = True      'Lock all cells
                        .Locked = False             'Unlock just Found row
                    End With
                Else
                    MsgBox "Couldn't match " & Range("KA1").Value & " in column A."
                End If
    
                ActiveSheet.Protect Password:="bioe1025"
                ActiveSheet.EnableSelection = xlUnlockedCells
                
                Application.ScreenUpdating = True
                Exit Sub
                
            End If
    
    '===============================================================================
    
    
        ElseIf i = 6 Then 'YES
    
    ' TRANSFER CONICAL-1 DATA AND OVERWRITE EXISTING DATA
    
            If Range("Z18").Value = 1 Then
            
                File1 = ActiveWorkbook.Name
                
                Application.ScreenUpdating = False
                
                'open QC Data trendline workbook
                
                ChDir "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB QC\510k Lot Testing\PCB QC Trendline Data"
                Workbooks.Open Filename:= _
                    "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB QC\510k Lot Testing\PCB QC Trendline Data\PCB Conicals Trendline.xlsm"
                
                ActiveSheet.Unprotect Password:="bioe1025"
                Range("KA1").ClearContents
                
                Range("KA3").ClearContents
                Range("KA3").Value = 1
                
                Workbooks(File1).Activate
                
                Sheets("QC5003.4A-1 FINAL QC CONICAL").Visible = True
                Sheets("QC5003.4A-1 FINAL QC CONICAL").Range("Z15").Copy
                Workbooks("PCB Conicals Trendline.xlsm").Activate
                Sheets("Conicals").Range("KA1").PasteSpecial Paste:=xlPasteValues
                
                
                Workbooks(File1).Activate
                ' copy data
                Sheets("Conical Data Transfer").Visible = True
                Sheets("Conical Data Transfer").Range("A2:JQ2").Copy
                
                Workbooks("PCB Conicals Trendline.xlsm").Activate
                
                'Loop to find row
                Range("A2").Select
            
                Do
                
                    If ActiveCell.Value <> Range("KA1").Value Then
                        Selection.Offset(1, 0).Select
                    End If
                    
                Loop Until ActiveCell.Value = Range("KA1").Value
                    
                ActiveCell.Offset(0, 1).Select
                ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                        :=False, Transpose:=False
                Application.CutCopyMode = False
                
            End If
                    
            ActiveSheet.Protect Password:="bioe1025"
            
            ActiveWorkbook.Save
            ActiveWorkbook.Close
            
            Workbooks(File1).Activate
            
            Sheets("QC5003.4A-1 FINAL QC CONICAL").Range("R35").Value = "Updated" & "-" & Date
            Call Intro_page
            
            MsgBox "Trendline Data has been updated."
            
            Application.ScreenUpdating = True
            
            '-------------------------------------------------------------------------------
            
            'UPDATE CONICAL-2 DATA
            
        ElseIf Range("Z18").Value = 2 Then
            
            File1 = ActiveWorkbook.Name
            
            Application.ScreenUpdating = False
            
            'open QC Data trendline workbook
            
            ChDir "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB QC\510k Lot Testing\PCB QC Trendline Data"
            Workbooks.Open Filename:= _
                "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB QC\510k Lot Testing\PCB QC Trendline Data\PCB Conicals Trendline.xlsm"
            
            ActiveSheet.Unprotect Password:="bioe1025"
            Range("KA1").ClearContents
            
            Range("KA3").ClearContents
            Range("KA3").Value = 2
            
            Workbooks(File1).Activate
            
            Sheets("QC5003.4A-2 FINAL QC CONICAL").Visible = True
            Sheets("QC5003.4A-2 FINAL QC CONICAL").Range("Z15").Copy
            Workbooks("PCB Conicals Trendline.xlsm").Activate
            Sheets("Conicals").Range("KA1").PasteSpecial Paste:=xlPasteValues
            
            
            Workbooks(File1).Activate
            ' copy data
            Sheets("Conical Data Transfer").Visible = True
            Sheets("Conical Data Transfer").Range("A4:JQ4").Copy
            
            Workbooks("PCB Conicals Trendline.xlsm").Activate
            
            'Loop to find row
            Range("A2").Select
            
            Do
            
                If ActiveCell.Value <> Range("KA1").Value Then
                    Selection.Offset(1, 0).Select
                End If
            
            Loop Until ActiveCell.Value = Range("KA1").Value
            
            ActiveCell.Offset(0, 1).Select
            ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
            Application.CutCopyMode = False
            
        End If
            
        ActiveSheet.Protect Password:="bioe1025"
        
        ActiveWorkbook.Save
        ActiveWorkbook.Close
        
        Workbooks(File1).Activate
        
        Sheets("QC5003.4A-2 FINAL QC CONICAL").Range("R35").Value = "Updated" & "-" & Date
        Call Intro_page
        
        MsgBox "Trendline Data has been updated."
        
        Application.ScreenUpdating = True
    
    'End If - these are NOT part of any structure
    'End If - these are NOT part of any structure
    
    Else
        Call Trendline_update_Conicals
    End If

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
How to use conditional compiling in VBA
Replies
4
Views
462
Jon Peltier
J
A
  • Question Question
Procedure works in one workbook but not another
Replies
3
Views
74
AlexanderBB
A
E
  • Question Question
Opening a User Form from Custom Ribbon Button Run-time and Complie issues
Replies
3
Views
503
Micron
M
B
  • Question Question
Using VBA to retrieve specific pages from PDFs
Replies
1
Views
198
Big_Al_fae_the_rigs
B
M
  • Question Question
Space between paragraphs from a cell in wb to bookmark in word.
Replies
0
Views
234
molesy01
M

Forum statistics

Threads
1,224,574
Messages
6,179,628
Members
452,933
Latest member
patv

Share this page

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
Back
Top