Excel VBA Compile Error: Else without If

[Popsicle]

Hello All,

I am trying to use a check-box macro named, "AGLFAqueRun" and I am using an If Else statement to use it. When checked, I want it to make a few cells white with black font and unlocked from protected. Then, when checked, the cell returns to it's original form: orange box with red font and some values that I have set. Posted below is the code I have, excluding the variables I have established. I am running Office 2010 with Windows 7.

Excel Code:

---

If AGLFAqueRun = True Then
        Max.Locked = False
        Min.Locked = False
        With Max
            .NumberFormat = "0.00"
         With Max.Interior
            .ColorIndex = 1
         With Max.Font
            .Color = vbBlack
        End With
        With Min
            .NumberFormat = "0.00"
         With Min.Interior
            .ColorIndex = 1
         With Max.Font
            .Color = vbBlack
        End With
     [I][B][COLOR=#000000] Else[/COLOR][/B][/I]
        With Max.Interior
            .ColorIndex = 46
         With Max.Font
            .Color = vbRed
        End With
        With Min.Interior
            .ColorIndex = 46
         With Min.Font
            .Color = vbRed
        End With
        Bmax = TrueBmax
        Bmin = TrueBmin
        Cmax = TrueCmax
        Cmin = TrueCmin
        Dmax = TrueDmax
        Dmin = TrueDmin
        Emax = TrueEmax
        Emin = TrueEmin
        Max.Locked = True
        Min.Locked = True
    End If

End Sub

---

The bold and italicized portion is what the debugger highlights as blue when I run the code.

Thank you,
Popsicle

 

[StephenCrump]

Welcome to MrExcel!

As a start, you have too many With and not enough End With. With this block, for example, you need three End With to close.

With Max
    .NumberFormat = "0.00"
   With Max.Interior
        .ColorIndex = 1
        With Max.Font
            .Color = vbBlack
        End With
    End With
End With

Perhaps simplify to:

With Max
    .NumberFormat = "0.00"
    .Interior.ColorIndex = 1
    .Font.Color = vbBlack
End With

 

[Popsicle]

Thank you for the tip! That looks much better. However, I am still getting the same error after implementing your suggestion.

 

[StephenCrump]

I think I could made it run by just cleaning up the With blocks (i.e. more than one).

Can you post your revised code?

 

[pjmorris]

Hi Popsicle,

I think you need to show us all the code as it is likely the problem is elsewhere in the procedure. Stephen's suggestions are a good improvement, but haven't resolved the main problem. As a thought, do you have Option Explicit as the very first line in your module? If not it is a good idea to include it as this will ensure you have declared all variables before use, which can avoid some mistakes that are difficult to find otherwise (note it can also be selected for all future code by putting a tick in the box next to 'Require Variable Declaration' in VBA Tools, Options dialogue.


Hope this helps a bit.

 

[Popsicle]

Hello pjmorris,

Thank you for your quick reply. I implemented both of your suggestions and I am still getting the same error. Here is the full code:

Option Explicit
Private Sub AGLFAqueRun_Click()
    Dim Max As Object, Min As Object, Bmax As Variant, Bmin As Variant, Cmax As Variant, _
    Cmin As Variant, Dmax As Variant, Dmin As Variant, Emax As Variant, Emin As Variant, _
    TrueBmax As Variant, TrueBmin As Variant, TrueCmax As Variant, TrueCmin As Variant, _
    TrueDmax As Variant, TrueDmin As Variant, TrueEmax As Variant, TrueEmin As Variant, _
    Batch As Object
        
        Set Batch = Range("C3")
        Set Max = Range("N21:N24")
        Set Min = Range("P21:P24")
        Bmax = Range("N21")
        Bmin = Range("P21")
        Cmax = Range("N22")
        Cmin = Range("P22")
        Dmax = Range("N23")
        Dmin = Range("P23")
        Emax = Range("N24")
        Emin = Range("P24")
        TrueBmax = Range("U21")
        TrueBmin = Range("V21")
        TrueCmax = Range("U22")
        TrueCmin = Range("V22")
        TrueDmax = Range("U23")
        TrueDmin = Range("V23")
        TrueEmax = Range("U24")
        TrueEmin = Range("V24")
        
        
    If AGLFAqueRun = True Then
        Max.Locked = False
        Min.Locked = False
        Batch = "MOC"
        With Batch.Font
            .Size = 20
            .Italic = True
        With Max
            .NumberFormat = "0.00"
            .Interior.ColorIndex = 1
            .Font.Color = vbBlack
        End With
        With Min
            .NumberFormat = "0.00"
            .Interior.ColorIndex = 1
            .Font.Color = vbBlack
        End With
      Else
        Batch.Clear
        With Max
            .Interior.ColorIndex = 46
            .Font.Color = vbRed
        End With
        With Min
            .Interior.ColorIndex = 46
            .Font.Color = vbRed
        End With
        Bmax = TrueBmax
        Bmin = TrueBmin
        Cmax = TrueCmax
        Cmin = TrueCmin
        Dmax = TrueDmax
        Dmin = TrueDmin
        Emax = TrueEmax
        Emin = TrueEmin
        Max.Locked = True
        Min.Locked = True
    End If
    
    
End Sub

Thank you,
Popsicle

P.S. I'm brand new to coding so I'd like your guys' critique on my format. Thanks again.

 

[StephenCrump]

Quick answer:

        With Batch.Font
            .Size = 20
            .Italic = True
        [B]End With[/B]

There's other changes I'd suggest to your code ... but I'm heading out now for a few hours.

 

[pjmorris]

Hi Stephen has identified the cause of your error.

Other comments I might offer are:

1. To declare a Variant you only need to write Dim Bmax, Bmin, etc as the default declaration is Variant. However this is not good code as you haven't really constrained what the variable is for. So it would be reasonable to use Range to define variables that hold ranges only, integers for counting loop iterations, etc.

2. Not sure what you are attempting to achieve with the Bmax=TrueBmax line. If you want the contents of Cell U21 (TrueBmax) to be copied to N21 (Bmax) then declaring Bmax and TrueBmax as Range will help you then have:

Set Bmax = Range("N21")
Set TrueBmax = Range("U21")

this line Bmax=TrueBmax then copies the contents of cell U21 to cell N21. As currently written although the value of TrueBmax is copied to Bmax it seems a pointless activity since the next part of the code terminates the procedure and hence the new value of Bmax is not used?

Hope this makes sense.

Regards

 

[Popsicle]

Hello All,

Thank you for your suggestions. I used them all and now, the code works great; no more problems!

Thank you,
Popsicle

 

[pjmorris]

excellent, thanks for the feedback.