Else If Error

[KatKitKat]

Hi,
Having trouble with some VBA coding and can't figure out my error. I have a spreadsheet that users will need to filter based on selections they make then select a button to run the code. I added data validation lists in cells D2 and E2 that will give the options to filter the list (both conditions to be met. The VBA will filter the list and do a few other things like insert values, names, etc. There are approximately 18 combinations of criteria so I chose to do the filtering this way.

Below is the start of my code. I am getting a "Compile Error Else without If" error and I can't figure out my mistake. Below is the code so far which just has 3 of the possible combinations of criteria. The code gets hung up on the first "ElseIf" line ("ElseIf list1 = "Construction Contingency" And list2 = "Open" Then").

Any help would be appreciated.
Thanks!

Sub Button21_Click() 'filter Use Log control button by SOF and Status
    ActiveSheet.Unprotect Password:="1234"
    Dim list1 As String, list2 As String
    
    list1 = Range("D2")
    list2 = Range("E2")
        
    If list1 = "Construction Contingency" And list2 = "Used" Then
        With ActiveSheet.Range("A12:J1010")
        .AutoFilter Field:=5, Criteria1:="Construction Contingency"
        .AutoFilter Field:=6, Criteria2:="Closed"
        Range("A7") = Sheets("ExposureLog").Range("I2:I2") & (" - ") & Range("D2") 'set for all transactions
        Range("I6") = Sheets("Table").Range("E68") 'set for construction contingency
        Range("I7") = Sheets("Table").Range("E69") 'set for construction contingency
        Range("I8") = Sheets("Table").Range("E70") 'set for construction contingency
        Range("A9") = Range("E2") & (" ") & ("CONTINGENCY TRANSACTIONS") 'set for all transactions - may need IF statement to add note that pending means open
        ElseIf list1 = "Construction Contingency" And list2 = "Open" Then
        With ActiveSheet.Range("A12:J1010")
        .AutoFilter Field:=5, Criteria1:="Construction Contingency"
        .AutoFilter Field:=6, Criteria2:="Open"
        Range("A7") = Sheets("ExposureLog").Range("I2:I2") & (" - ") & Range("D2") 'set for all transactions
        Range("I6") = Sheets("Table").Range("E68") 'set for construction contingency
        Range("I7") = Sheets("Table").Range("E69") 'set for construction contingency
        Range("I8") = Sheets("Table").Range("E70") 'set for construction contingency
        Range("A9") = Range("E2") & (" ") & ("CONTINGENCY TRANSACTIONS") 'set for all transactions - may need IF statement to add note that pending means open
        ElseIf list1 = "Construction Contingency" And list2 = "All" Then
        With ActiveSheet.Range("A12:A1010")
        .AutoFilter Field:=5, Criteria1:="Construction Contingency"
        Range("A7") = Sheets("ExposureLog").Range("I2:I2") & (" - ") & Range("D2") 'set for all transactions
        Range("I6") = Sheets("Table").Range("E68") 'set for construction contingency
        Range("I7") = Sheets("Table").Range("E69") 'set for construction contingency
        Range("I8") = Sheets("Table").Range("E70") 'set for construction contingency
        Range("A9") = Range("E2") & (" ") & ("CONTINGENCY TRANSACTIONS") 'set for all transactions - may need IF statement to add note that pending means open
    End If
End Sub

 

[Fluff]

You need an End With before each ElseIf & before the End If

 

[KatKitKat]

You need an End With before each ElseIf & before the End If

Thank you for your reply. I appreciate it.

I added the Wnd With statement as you describe (duh!), but now nothing is happening. No error message, no filtering, nothing. Not sure what is happening and I am getting mentally fried. I am sorry to impose, but can you think of a reason why this wouldn't work? I added a form control button in the worksheet to run the code and placed the code in a module. Frazzled as I am a beginner at this.
Kathleen

 

[Fluff]

Is the button on the same sheet as the data?
If so try stepping through the code using F8 to see what is happening.

 

[KatKitKat]

Is the button on the same sheet as the data?
If so try stepping through the code using F8 to see what is happening.

The button is on the same sheet as the data. I did step through using F8 and it highlighted several lines but nothing happened. When a line is highlighted during this process, what does that mean? Again, I am a self taught newbie at this so my knowledge is rudimentary. Signed up for some courses online to hope to improve. Tx. K

 

[Fluff]

When a line is highlight, that is the next line to be processed.
When you stepped through did it just go from If to Elesif to elseif to endif, or did it go into the sections to run the filter?

 

[KatKitKat]

The button is on the same sheet as the data. I did step through using F8 and it highlighted several lines but nothing happened. When a line is highlighted during this process, what does that mean? Again, I am a self taught newbie at this so my knowledge is rudimentary. Signed up for some courses online to hope to improve. Tx. K

The rows highlighted during the F8 were my ActiveSheet. Unprotected line, the 2 lists, and then the If and Else If lines. Sounds like I've done something wrong and may be back at the drawing board

 

[KatKitKat]

When a line is highlight, that is the next line to be processed.
When you stepped through did it just go from If to Elesif to elseif to endif, or did it go into the sections to run the filter?

Yes. Just replied below to myself. The ActiveSheet. Unprotected, the 2 lists, the If and the Else Ifs

 

[Fluff]

Ok, in that case the cell values do not what you have in the code. Make sure the the cells do not have any leading/trailing spaces, or typos. Also VBA is case sensitive so Open is not the same as open

 

[KatKitKat]

OOhhhhh....that makes sense. I have Open in the code but in the field it is OPEN. Thank you. I wasn't thinking about the sensitivity of cases. I will get back to this and try again. I will let you know the outcome.

I appreciate your patience and helping me with this. Like I said, signed up for some online classes and bought a book on VBA because it seems like such a powerful tool. I hope to be able to answer other people's questions soon. Have a great night!
K