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

 

[StephenCrump]

On further thought (dealing with immediate problem and missing the bigger picture!) ...

Why do you need this code at all?

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

Can't this formula stay permanently in Q5?

EDIT: And could also usefully be replaced with an HLOOKUP?