VBA code returning compile error for if statement

Question

Here is a brief explanation of my Code on Sheet1 (I am very new to VBA);

I have three workbooks, one workbook (with Sheet1 being the codename of the sheet named "Operator" where the VBA code is written in), and another two external workbooks with different file paths called "Changes" (file path is the: Database_IRR 20-2S New.xlsm in my code, this is the CHANGES Database) and "HE171" (file path is the: Technology_Changes\Changes_Database_IRR_20-2S_New.xlsm in my code, this is the MAIN Database).

1) IF the operator hits "YES" on Commandbutton1, I want the code to check if the value in cell "H4" from Sheet1 is present in Column A of the "HE 171" sheet from the MAIN Database then,

2) IF the value in "H4" is PRESENT in the MAIN Database, I want the code to check if the value in cell "H4" from Sheet1 is present in Column A of the "Changes" sheet from the CHANGES Database and IF the value of "H4" is PRESENT in the "CHANGES" sheet I want the code to use Module 13 (I have not posted it) to set the date and time stamp in two columns of the "CHANGES" sheet, and Module 8 to send over the values from certain Column "K" to cells inside of the "CHANGES" sheet (For example, I want module 8 to filter the value of "H4" in Column A and have it in row 2 as row 1 has my headings, and place the value of "K30" from Sheet1 to cell (1,6) in the "CHANGES" sheet)

2.1) IF the value in "H4" is PRESENT in the MAIN Database, and IF the value of "H4" is NOT Present in the "CHANGES" sheet I want the code to use Module 14 (have not posted it) to add the value of "H4" into Column A of a NEW row in the "CHANGES" sheet in the CHANGES Database, Module 13 (I have not posted it) to set the date and time stamp in two columns of the "CHANGES" sheet, and Module 8 to send over the values from certain Column "K" to cells inside of the "CHANGES" sheet

3) IF the value in "H4" is NOT Present in the MAIN Database, I want the code to use Module 7 (have not posted it) to add the value of "H4" into Column A of a NEW row in the "HE 171" sheet in the MAIN Database, Module 14 (have not posted it) to add a the value of "H4" into Column A of a NEW row in the "CHANGES" sheet, Module 13 (I have not posted it) to set the date and time stamp in two columns of the "CHANGES" sheet, and Module 8 to send over the values from certain Column "K" to cells inside of the "CHANGES" sheet

5) IF the operator hits "NO" or "x" on Commandbutton1, I want the code to save and close both external workbooks (The MAIN Databse & CHANGES Database) with a password, and then just protect Sheet1 and keep it open with nothing cleared

    Option Explicit


    Dim Cd As Workbook
    Dim Md As Workbook

    Dim Changes As Worksheet
    Dim HE171 As Worksheet

    Dim nConfirmation As Integer

    'Actions for when the "Confirm Changes" button is clicked
    Private Sub CommandButton1_Click()


        Set Cd = Workbooks.Open("\FILEPATH/Technology_Changes\Changes_Database_IRR_20-2S_New.xlsm")
        Set Md = Workbooks.Open("\FILEPATH\Database_IRR 20-2S New.xlsm")


        Set Changes = Cd.Sheets("Changes")

        On Error Resume Next

        Set HE171 = Md.Sheets("HE 171")


        'Creating the "Yes or No" message box displayed when operators click the "Confirm Changes" button on the Operator Sheet
        nConfirmation = MsgBox("Do you want to send a notification about the sheet update?", vbInformation + vbYesNo, "Sheet Updates")

        'Declares the variable for the string that we will be finding, which is the key in this case (for the With statement)
        Dim FindString As String

        'Declares the variable for the range in which we will be locating the string (for the With statement)
        Dim RNG As Range

        'Sets the string we need to find as the key value which is in cell "H4" of the Operator sheet (for the With Statement)
        FindString = Sheet1.Range("H4").Value

        'Actions if "YES" is clicked when the "Confirm Changes" button is clicked on the Operator Sheet
        If nConfirmation = vbYes Then

            'Opens and activates the Main Database workbook, with "HE 171" as the active sheet
            HE171.Activate

            'Temporarily unprotects the Main Database Workbook and Operator sheet (this is the sheet the code is in)
            ActiveSheet.Unprotect "Swrf"
            Sheet1.Unprotect "Swrf"

            'Searches all of column A in the Main Database in sheet "HE 171" for the string(key)
            With ActiveSheet.Range("A:A")            'searches all of column A
                Set RNG = .Find(What:=FindString, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                'End With
                '////////////////////////////////////////////////////////////////////////////

                'Actions if the key is present in column A of the MAIN database
                If Not RNG Is Nothing Then

                    'Since Key is present in main database, now opens and sets the Changes_Database "Changes" Sheet as active contents
                    Changes.Activate

                    'Temporarily unprotects the Changes_Database
                    ActiveSheet.Unprotect "Swrf"

                    'Declares the variable for the string that we will be finding, which is the key in this case (for the With statement)
                    Dim FindString2 As String

                    'Declares the variable for the range in which we will be locating the string (for the With statement)
                    Dim RNG2 As Range

                    'Sets the string we need to find as the key value which is in cell "H4" of the Operator sheet (for the With Statement)
                    FindString2 = Sheet1.Range("H4").Value

                    'Searches all of column A in the Changes_Database "Changes" sheet for the string(key)
                    With ActiveSheet.Range("A:A")    'searches all of column A
                        Set RNG2 = .Find(What:=FindString, _
                                         After:=.Cells(.Cells.Count), _
                                         LookIn:=xlValues, _
                                         LookAt:=xlWhole, _
                                         SearchOrder:=xlByRows, _
                                         SearchDirection:=xlNext, _
                                         MatchCase:=False)


                        'Actions if the key is present in column A of the Changes_Database (So a change request was previously made for the key and it already has a row in the "Changes" sheet)
                        If Not RNG2 Is Nothing Then

                            'Calls module 13 to set the date and time of the requested change in the "Changes" sheet
                            Call TimeStamp

                            'Calls module 8 to send over the requested changes to the "Changes" sheet
                            Call SendChanges

                            'On Error Resume Next

                            'Protects the Changes_Database
                            ActiveSheet.Protect "Swrf"

                            '////////////////////////////////////////////////////////////////////////////

                            'Actions if the key DOES NOT exist in column A of the Changes_Database


                        Else

                            'Module 14: Adds a new row with the key to the Changes_Database
                            Call NewPart2

                            'Calls module 13 to set the date and time of the requested change in the "Changes" sheet
                            Call TimeStamp

                            'On Error Resume Next

                            'Calls module 8 to send over the requested changes to the "Changes" sheet
                            Call SendChanges

                        End If

                    End With

                Else

                    'Module 7:  Adds a new row with the key to the MAIN Database
                    Call NewPart

                    'Module 14: Adds a new row with the key to the Changes_Database
                    Call NewPart2

                    'Module 13: to set the date and time of the requested change in the "Changes" sheet
                    Call TimeStamp

                    'Module 10: Fills in the date and time the key was created for the "HE 171" sheet
                    Call TimeStamp2

                    'On Error Resume Next

                    'Calls module 8 to send over the requested changes to the "Changes" sheet
                    Call SendChanges

                End If

            End With


            'Actions if "No" is clicked when the "Confirm Changes" button is clicked on the Operator Sheet
        Else

            '''''''If nConfirmation = vbNo Then


            'Protects Changes_Database (as it was activated after the Main Database and is therefore the active contents and saves/closes it
            Changes.Activate
            ActiveSheet.Protect "Swrf"
            ActiveWorkbook.Save
            ActiveWorkbook.Close SaveChanges:=True

            'Sets Main Database as active contents to protect it, save it and close it
            HE171.Activate
            ActiveSheet.Protect "Swrf"
            ActiveWorkbook.Save
            ActiveWorkbook.Close SaveChanges:=True

            'Protects Operator Sheet and saves it
            Sheet1.Protect "Swrf"
            'Workbook.Close SaveChanges:=True

        End If

    End Sub

And here is module 8, currently my code is not pasting an values from column k in my current workbook (this is where the VBA code is written in, within sheet1 titled "Operator" in the workbook).

    'Module 8: Sends the requested changes over to the "Changes" sheet

    Sub SendChanges()

        Set Cd = Workbooks.Open("\FILEPATH\Technology_Changes\Changes_Database_IRR_20-2S_New.xlsm")
        Set Changes = Cd.Sheets("Changes")

        Changes.Activate
        ActiveSheet.Unprotect "Swrf"

        '////////////////////////////////////////////////////////////////////////////'

        'Only executes this macro if the the new/change requested value in column "K" of the Operator sheet has a numerical value present
        If Sheet1.Range("K30").Value <> "" Then


            'Filters the Changes_Database for the part name & process (the key) which is in cell "H4" of the Operator sheet
            ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:=Sheet1.Range("H4")


            'Copies the changed content in cell "K30" from the Operator Sheet
            Sheet1.Range("K30").Copy

            'Finds the row in the Changes_Database that has matched all filters and;
            'Pastes the value of cell "K30" into the matching parameter cell in the Changes_Database,which is in column 6 in this case
            ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 6).PasteSpecial xlPasteValues


            'Removes all filters and shows all data'
            ActiveSheet.ShowAllData


        End If
        '////////////////////////////////////////////////////////////////////////////'


        'Repeats the If and Else code bordered with slashes "////", for all parameter changes in the K column ("KXX")'
        If Sheet1.Range("K31").Value <> "" Then
            ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:=Sheet1.Range("H4")
            Sheet1.Range("K31").Copy
            ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 7).PasteSpecial xlPasteValues

            ActiveSheet.ShowAllData
        End If


        If Sheet1.Range("K32").Value <> "" Then
            ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:=Sheet1.Range("H4")
            Sheet1.Range("K32").Copy
            ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 8).PasteSpecial xlPasteValues

            ActiveSheet.ShowAllData
        End If

        'On Error Resume Next

        Sheet1.Range("K30:K115").ClearContents

        'On Error Resume Next

        ActiveSheet.Protect "Swrf"
        ActiveWorkbook.Save
        ActiveWorkbook.Close SaveChanges:=True

    End Sub

Suggestion: Indenting your code properly would help find the problem. — Brian M Stafford, Nov 19 '19 at 14:07
^ Check out this [smart indenter](http://rubberduckvba.com/indentation). — BigBen, Nov 19 '19 at 14:08
You're not closing your `If...End If` statements. Read more [here](https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/if-then-else-statement) to better understand it's utility. — Dean, Nov 19 '19 at 14:08
You can start by indenting your code. Usually these types of issues will become immediately apparent if you do. And please remove the `On error Resume next` . this only serves to hide your mistakes from you. — Geert Bellekens, Nov 19 '19 at 14:08
Thank you BigBen for that smart indenter. I have edited the code with some idents. and Dean De Villiers... that link is not helpful at all I'm sorry, I am clearly saying that I know that my end if is missing somewhere... that's the point of this post... plus I have checked out that link previously and it doesn't explain with and if statements nested... — Handreen, Nov 19 '19 at 14:14
You're missing an `End With`. And make sure the `End With` lines up with the `With`. — BigBen, Nov 19 '19 at 14:18
Use `On Error Resume Next` with caution. There are cases where it is necessary, but otherwise you are masking errors that may need fixing. — Darrell H, Nov 19 '19 at 14:48
Big Ben I don't think I am missing an End With statement anymore now! I just need help with the function of my code now. — Handreen, Nov 20 '19 at 19:23

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

0

If you strip out everything that doesn't start or finish a block of statements, you are left with this code:

Private Sub CommandButton1_Click() '#1
    If nConfirmation = vbYes Then ' #2
        With ActiveSheet.Range("A:A") ' #3
            If Not RNG Is Nothing Then ' #4
                With ActiveSheet.Range("A:A") ' #5
                    If Not RNG Is Nothing Then ' #6
                    Else ' #6.1
                    End If ' #7
                ' ##### THERE SHOULD BE AN END WITH HERE ####
            Else ' #4.1
            End If ' #8
        End With ' #9
    Else ' #2.1
    End If ' #10
End Sub ' #11

Every time you start a new block, you can think of adding the new block to the top of a stack of block statements. Whenever you close a block, you have to close the block that is currently on top of the stack. If there is a mismatch (e.g. the block on top of the stack is a With block but you try to close it using an End If), an error will occur

I've added numbers to each line in your program (with a slight variation for the Else statements). Here's how that stack would look after each line is executed until we get to the line that's causing the error:

1:

Sub from #1

2:

If from #2
Sub from #1

3:

With from #3
If from #2
Sub from #1

4:

If from #4
With from #3
If from #2
Sub from #1

5:

With from #5
If from #4
With from #3
If from #2
Sub from #1

6:

If from #6
With from #5
If from #4
With from #3
If from #2
Sub from #1

6.1:

The Else from #6.1 replaces the If from #6

Else from #6.1
With from #5
If from #4
With from #3
If from #2
Sub from #1

7:

The End If at #7 matches with the Else at #6.1

With from #5
If from #4
With from #3
If from #2
Sub from #1

8:

Error: The Else at #4.1 fails to match with the With at #5 which is on top of the stack. This Else actually relates to the If at #4

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 19 '19 at 22:15

barrowc

10,444
1
40
53

Thank you so much for your detailed explanation and visual, I appreciate the effort and it really helped! I have adjusted my code accordingly (with respect to what you have displayed) and I have indented everything to align, along with including the End with statement where you recommended. My only issue now is that the other two worksheets from the external workbooks, "Changes" sheet from one workbook and "HE171" sheet from another workbook, remain open and don't paste any values from my original sheet. Could you please take a look at my edited code and module 8 – Handreen Nov 20 '19 at 15:02
I have added module 8 to the bottom of my original code, can you please have a look? – Handreen Nov 20 '19 at 15:20
You need to start a new question because you've moved on to a different issue. At first glance though, you refer to `Sheet1` several times but what `Sheet1` refers to depends on which workbook is active at the time. Try reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/2127508). Also comment out all of the `On Error Resume Next` lines and make sure that the password you are protecting/unprotecting the worksheet with is consistent (there's at least one occurrence of "Swarf" instead of "Swrf") – barrowc Nov 20 '19 at 21:46
Thank you for your comment, I made up a random password just for the purpose of posting it (in my code I have the same password everywhere) but thanks for noticing that. Also, for some reason I couldn't post because my previous question was downvoted so I just updated my current question as it told me to edit my questions to improve them. I will read that post that you have given, hopefully I can understand active sheets a little better. My understanding is that Sheet1 always refers to "Sheet 1" of the workbook the code is being written on. – Handreen Nov 21 '19 at 14:50
To refer to the worksheet named "Sheet1" in the workbook where the code is written, use `ThisWorkbook.Worksheets("Sheet1")`. If you just refer to `Sheet1` then that's the sheet with code name "Sheet1" in whichever workbook is active. The actual sheet name could be anything because the code name and sheet name don't need to match – barrowc Nov 21 '19 at 22:19
The codename for the worksheet where the code is written is "Sheet1", the actual sheet name is "Operator" but my understanding is that using the sheet's code name is a better option in the case of future changes? Please correct me if my understanding is wrong. Also, if I activate another workbook, does the codename Sheet1 not reference the workbook where the code is written anymore? – Handreen Nov 22 '19 at 02:13
If you activate another workbook then "Sheet1" will now reference the sheet with that codename in the newly active workbook. You can easily test that using the Immediate window in the VBA IDE. Use `ThisWorkbook.Worksheets("Operator")` to consistently refer to the worksheet "Operator" in the same workbook where the code is written. I find it easier to use the actual sheet names in VBA but you can use the codenames instead – barrowc Nov 22 '19 at 19:15

VBA code returning compile error for if statement

1 Answers1

1:

2:

3:

4:

5:

6:

6.1:

7:

8: