Cannot resolve compile error - EndIf without block If

[brought]

I am getting an "EndIf without block If" compile error. If anyone is able to assist I would greatly appreciate it.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("O2").Address Then
Range("P2:U2,O5:P5").ClearContents
End If


If Target.Address = Range("O5").Address Then
Range("P5:Q5").ClearContents
End If

If Intersect(Target, Range("P$5$")) Is Nothing Or Target.Value = "" Then Exit Sub
Application.EnableEvents = False

Range("Q5").Formula = "=IF(COUNTBLANK(P5),"" "", IF(P5=AI74,CBLT_TGD0_CB, IF(P5=AJ74,SBLT_LONG_TGD_TMGL_CB, IF(P5=AK74,SBLT_Short_TGD1_CB, IF(P5=AL74,CBLT_TGD1_CB, IF(P5=AM74,AM75, IF(P5=AN74,CBLT_TGD2_CB, IF(P5=AO74,CBLT_TGD3_CB, IF(P5=AP74,SBLT_SHORT_TMGL_CB, IF(P5=AQ74,CBLT_TMGL1_CB, IF(P5=AR74,CBLT_TMGL2_CB, IF(P5=AS74,CBLT_TMGL2A_CB, IF(P5=AT74,CBLT_TGL1_CB, IF(P5=AU74,CBLT_TGL2_CB, IF(P5=AV74,CBLT_TGL2A_CB, IF(P5=AW74,CBLT_TGL3_CB,""ERROR""))))))))))))))))"
End If

Application.EnableEvents = True
End Sub

 

[rfletcher35]

Looking at it on your 4th section down you have 15x IF's, my understanding is that you require an "END IF" for each IF

 

[brought]

the additional if's are for the formula which sits in the cell...for some instances, I need the user to overwrite the data which is why I added this code to reset the formula in case the drop down list in P5 changes.

 

[silentwolf]

Hi I am not to sure but maybe the last end if is to much

 

[brought]

Thanks but removing the last endif did not work.

 

[StephenCrump]

Thanks but removing the last endif did not work.

If you take out the last End If (i.e. 3rd last line of your code as posted) as silentwolf suggested, you should eliminate the compile error.

When you say "did not work", I assume you're getting a run-time error 1004?

That's because you have an invalid range reference: Range("P$5$")

Just change to Range("P5").

 

[brought]

I removed the End If and changed Range("P$5$) to Range("P5"). It solved the compile errors but clear.contents is no longer working and my function in Q5 is not refreshing when I change cell P5. I am going to convert to a vlookup function for Q5 and insert that into the code versus the nested if statement.

 

[brought]

Correction....I get a type mismatch on this line

If Intersect(Target, Range("P5")) Is Nothing Or Target.Value = "" Then Exit Sub

 

[Michael M]

Dont have Excel at the moment but try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("O2") Then
Range("P2:U2,O5:P5").ClearContents
End If
If Target.Address = Range("O5") Then Range("P5:Q5").ClearContents
If Intersect(Target, Range("$P$5")) Is Nothing Or Range("O2").Value = "" Then Exit Sub
Application.EnableEvents = False

Range("Q5").Formula = "=IF(COUNTBLANK(P5),[color=red]""""[/color], IF(P5=AI74,CBLT_TGD0_CB, IF(P5=AJ74,SBLT_LONG_TGD_TMGL_CB, IF(P5=AK74,SBLT_Short_TGD1_CB, IF(P5=AL74,CBLT_TGD1_CB, IF(P5=AM74,AM75, IF(P5=AN74,CBLT_TGD2_CB, IF(P5=AO74,CBLT_TGD3_CB, IF(P5=AP74,SBLT_SHORT_TMGL_CB, IF(P5=AQ74,CBLT_TMGL1_CB, IF(P5=AR74,CBLT_TMGL2_CB, IF(P5=AS74,CBLT_TMGL2A_CB, IF(P5=AT74,CBLT_TGL1_CB, IF(P5=AU74,CBLT_TGL2_CB, IF(P5=AV74,CBLT_TGL2A_CB, IF(P5=AW74,CBLT_TGL3_CB,""ERROR""))))))))))))))))"

Application.EnableEvents = True
End Sub

I have also removed the space from your true value in the formula......putting a sapce in a cell will give you nightmares, if you want it blank !!!

 

[StephenCrump]

Correction....I get a type mismatch on this line

If Intersect(Target, Range("P5")) Is Nothing Or Target.Value = "" Then Exit Sub

If the original Target is O2 or O5, your code clears cells and hence triggers another call on Sub Worksheet_Change.
Target now contains more than one cell, and when you test Target.Value you get the error ("type mismatch" isn't the most helpful message).

You'll fix the immediate problem if you move Application.EnableEvents = False to the top of your Sub.

But you'll still get this error if the user changes more than one cell at a time. In general terms, depending on your preference, a Worksheet_Change Sub can either:

- Test that Target.Count = 1, or

- Loop through all cells in Target.

But in your specific case, perhaps:

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False
    
    If Not Intersect(Target, Range("O2")) Is Nothing Then _
        Range("P2:U2,O5:P5").ClearContents
    
    If Not Intersect(Target, Range("O5")) Is Nothing Then _
        Range("P5:Q5").ClearContents
    
    If Not Intersect(Target, Range("P5")) Is Nothing Then
        If Range("P5").Value <> "" Then _
            Range("Q5").Formula = "=IF(COUNTBLANK(P5),"""", IF(P5=AI74,CBLT_TGD0_CB, IF(P5=AJ74,SBLT_LONG_TGD_TMGL_CB, IF(P5=AK74,SBLT_Short_TGD1_CB, IF(P5=AL74,CBLT_TGD1_CB, IF(P5=AM74,AM75, IF(P5=AN74,CBLT_TGD2_CB, IF(P5=AO74,CBLT_TGD3_CB, IF(P5=AP74,SBLT_SHORT_TMGL_CB, IF(P5=AQ74,CBLT_TMGL1_CB, IF(P5=AR74,CBLT_TMGL2_CB, IF(P5=AS74,CBLT_TMGL2A_CB, IF(P5=AT74,CBLT_TGL1_CB, IF(P5=AU74,CBLT_TGL2_CB, IF(P5=AV74,CBLT_TGL2A_CB, IF(P5=AW74,CBLT_TGL3_CB,""ERROR""))))))))))))))))"
    End If
    
    Application.EnableEvents = True

End Sub