Where am I missing the Else without IF?

[mrbeanyuk]

Good morning. I have embedded a macro into Workbook_Open but in doing so I am prompted with 'Else without IF' error. It highlights the 'Else' line but I am unclear what further IF statement is needed? Any advice gratefully received.

Private Sub Workbook_Open()

Dim FR
Dim answer As Integer
Dim objInfo
Dim strLDAP
Dim strFullName
Dim LR
Set objInfo = CreateObject("ADSystemInfo")
strLDAP = objInfo.UserName
Set objInfo = Nothing
strFullName = GetUserName(strLDAP)

With Sheets("1718 KPIs")
   FR = .Cells(.Rows.Count, 2).End(xlUp).Row
    .Cells(2, 4).AutoFilter Field:=4, Criteria1:="Monthly"
 
answer = MsgBox("Would you like to go directly to your KPIs?", vbYesNo + vbQuestion, "KPIs")

If answer = vbYes Then

    With Sheets("1718 KPIs")
        LR = .Cells(.Rows.Count, 2).End(xlUp).Row
        .Cells(2, 7).AutoFilter Field:=7, Criteria1:=strFullName
     
Else
     'do nothing
End If
End With

'MsgBox "Select 'Show my KPIs'" & vbNewLine & "Once complete select 'Submit KPIs'", vbOKOnly

End Sub

 

[Johnny C]

If answer = vbYes Then

    With Sheets("1718 KPIs")
        LR = .Cells(.Rows.Count, 2).End(xlUp).Row
        .Cells(2, 7).AutoFilter Field:=7, Criteria1:=strFullName
     
     End With
Else
     'do nothing
End If

just the scope of the WITH should be before the else )

 

[mrbeanyuk]

Thanks! Worked a treat )

 

[Johnny C]

Another way, rather than using the With ... End With - which can get messy if there are a lot of nested IFs and Withs - is to set a Variable to the object you're using With for :-

Dim MySheet as Sheet

If answer = vbYes Then
    MySheet = Sheets("1718 KPIs")
    LR = MySheet.Cells(.Rows.Count, 2).End(xlUp).Row
    MySheet.Cells(2, 7).AutoFilter Field:=7, Criteria1:=strFullName
Else
     'do nothing
End If

This is more useful if you're looping through workbooks, sheets and cells/objects.

 

[Fluff]

@Johnny C
Shouldn't that be

Dim MySheet As [COLOR=#ff0000]Work[/COLOR]sheet

If Answer = vbYes Then
   [COLOR=#ff0000] Set [/COLOR]MySheet = Sheets("1718 KPIs")
    lr = MySheet.Cells(.Rows.Count, 2).End(xlUp).Row
    MySheet.Cells(2, 7).AutoFilter Field:=7, Criteria1:=strFullName
Else
     'do nothing
End If

 

[Johnny C]

Lol thanks Fluff, my bad!