My VBA IF statement is generation a compile error

Question

There is something wrong with my code and I can't seem to find it so I need another pair of eyes I think I'm going VBA Blind.

This is my code very simple if someone is doing a higher level then 3 columns are greyed out and n/a as they wont be getting any results for those modules in column D, F and G. If there not doing the higher level then it would be a reversed and they wont be getting results for modules in column B, C and E. There are 28 trainees so it would need to work for all rows.

The current code is as follows:

Private Sub Update()
    Dim Course As String
    Dataset = Range("A").Value

    If Course = "Higher" Then
        Range("D").Value = "N/A"
        Range("F").Value = "N/A"
        Range("G").Value = "N/A"
        Range("D,F,G").Select

        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.249977111117893
            .PatternTintAndShade = 0
    Else
            Range("B").Value = "N/A"
            Range("C").Value = "N/A"
            Range("E").Value = "N/A"
            Range("B,C,E").Select

            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = -0.249977111117893
                .PatternTintAndShade = 0
    End If
            End With

End Sub

However, I getting a compile error saying "Else without and If". As far as I can tell all the variables are declared the IF statement has been structured correctly.

Any thoughts?

It amazes me how many questions are asked because of incosistent indentation... — Martin, Jul 13 '21 at 10:57
`With` starts in between `IF .. Then` and `Else` and ends after `End If` — Maciej Los, Jul 13 '21 at 10:57

Dominique · Accepted Answer · 2021-07-13T11:06:43.170

Try this:

Private Sub Update()
    Dim Course As String
    Dataset = Range("A").Value

    If Course = "Higher" Then
        Range("D").Value = "N/A"
        Range("F").Value = "N/A"
        Range("G").Value = "N/A"
        Range("D,F,G").Select

        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.249977111117893
            .PatternTintAndShade = 0
        End With
    Else
            Range("B").Value = "N/A"
            Range("C").Value = "N/A"
            Range("E").Value = "N/A"
            Range("B,C,E").Select

            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = -0.249977111117893
                .PatternTintAndShade = 0
            End With
    End If

End Sub

If comes down to:

If ... Then
  ...
  With ...     'Start the first With-clause
    ...
  End With     'End the first With-clause
Else           'All With-clauses need to be closed before you can go here
  ...
  With ...     'Start the second With-clause
    ...
  End With     'End the second With-clause
End If         'All With-clauses needed be closed before you can go here.

While technically valid, that's a poor fix. It misses that the contents of the `with`s is identical, that there is [no need to select](https://stackoverflow.com/q/10714251/11683) and that the other actions too are the same, just applied to different ranges. — GSerg, Jul 13 '21 at 11:05
@GSerg: some things are selected, and nothing is done with them after all. That's indeed wrong, but it was my attempt to solve the compilation error first. — Dominique, Jul 13 '21 at 11:09
...which results now to an accepted answer using `Select` and `Selection`, plus unqualified ranges, plus duplicate code - best way to generate new newbie questions. — FunThomas, Jul 13 '21 at 11:49

score 0 · Answer 2 · answered Jul 13 '21 at 10:59

0

You have 2 With statements and only 1 End With.

Either move your With Selection.Interior above the first If and remove the second With.

or

Move your last End With above the End If.

answered Jul 13 '21 at 10:59

T4roy

196
6

There are indeed 2 `With` statements and only 1 `End With`, which is not the only problem with that code. The first of the two "either" would fix it, the second won't. – GSerg Jul 13 '21 at 11:13

My VBA IF statement is generation a compile error

2 Answers2