If Else statement not working

[Eurekaonide]

Can anyone tell me why my code below is erroring stating its got an Else without and IF please?
And why is it not looping through all my worksheets, it only puts the formula in on the first active worksheet ? Thanks ever so

Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
    With Ws
    If Range("O7").Value = "51" Then Ws.Range("AE2").Select
'With Selection.Interior
'.Pattern = xlSolid
'.PatternColorIndex = xlAutomatic
'.ThemeColor = xlThemeColorAccent6
'.TintAndShade = 0.799981688894314 ' green
' .PatternTintAndShade = 0
'End With
ActiveSheet.Range("2:2").AutoFilter Field:=2, Criteria1:="Actual"

Range("AE4").Select
ActiveCell.FormulaR1C1 = "=SUMIFS(Actuals!C9,Actuals!C4,R1C1,Actuals!C8,RC1)"
Range("AE4").Select
Selection.NumberFormat = "_-* #,##0.00_-;-* #,##0.00_-;_-* ""-""_-;_-@_-"
Selection.Copy
Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Select
ActiveSheet.Paste
ActiveSheet.Range("2:2").AutoFilter Field:=2
        Application.CutCopyMode = False
    ElseIf Range("O7").Value = "52" Then Ws.Range("AF2").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.799981688894314 ' green
.PatternTintAndShade = 0
End With
ActiveSheet.Range("2:2").AutoFilter Field:=2, Criteria1:="Actual"

Range("AF4").Select
ActiveCell.FormulaR1C1 = "=SUMIFS(Actuals!C9,Actuals!C4,R1C1,Actuals!C8,RC1)"
Range("AF4").Select
Selection.NumberFormat = "_-* #,##0.00_-;-* #,##0.00_-;_-* ""-""_-;_-@_-"
Selection.Copy
Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Select
ActiveSheet.Paste
ActiveSheet.Range("2:2").AutoFilter Field:=2
        Application.CutCopyMode = False
    Else: Range("O7").Value = "53a"
Ws.Range("AG2").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.799981688894314 ' green
.PatternTintAndShade = 0
End With
ActiveSheet.Range("2:2").AutoFilter Field:=2, Criteria1:="Actual"

Range("AG4").Select
ActiveCell.FormulaR1C1 = "=SUMIFS(Actuals!C9,Actuals!C4,R1C1,Actuals!C8,RC1)"
Range("AG4").Select
Selection.NumberFormat = "_-* #,##0.00_-;-* #,##0.00_-;_-* ""-""_-;_-@_-"
Selection.Copy
Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Select
ActiveSheet.Paste
ActiveSheet.Range("2:2").AutoFilter Field:=2
Application.CutCopyMode = False
End If
End With
Next Ws
MsgBox "Done"
End Sub

 

[RoryA]

This:

If Range("O7").Value = "51" Then Ws.Range("AE2").Select

is the single line form of If that doesn't need an End If (and any Else clause needs to be on the same line). It looks like you should have the part after the Then on a new line (and the same with your ElseIf line.

 

[jasonb75]

Can anyone tell me why my code below is erroring stating its got an Else without and IF please?

You have ElseIf in the 23rd line (think I counted correctly) but you have no block If preceding it.
The If in the 4th line is closed because the action is on the same line as the logical test.

This link shows you the correct ways to set out your vba If's Using If...Then...Else statements (VBA)

why is it not looping through all my worksheets,

You haven't qualified the ranges with a dot / period after the With ws line so the code is only ever working on the sheet that was active when the code started.

With statement (VBA)

Office VBA reference topic

docs.microsoft.com

Also, it would help if you indented your code, it makes it much easier to read.

 

[zweifuss]

Use the VBA Code Indenter Addin!
If you put a statment directly after Then, the If-statement ends with the line. No End If neccessary.
If you use Else: with colon (and mean it), always put End If or ElseIf in the next line.

 

[Chiwidan]

Could it be you got an
"With ..." in the third that doesn't have an "End With"

I am new to this so also learning...

Dan

 

[johnnyL]

@Chiwidan, this thread is nearly a year old. Not sure why it is being addressed, but there is an 'End With' for the third line, it is 3 lines up from the 'End sub' at the bottom of the code. The 'Else without If error' is exactly correct. the only previous 'If' in the code has a 'then' and instruction after it, thus ending that 'If' clause, as @RoryA and @jasonb75 stated.