If Then Else Not Executing Else

drhill78

New Member
Joined
Feb 21, 2019
Messages
12
I've trolled through the threads trying to gain some insight into this one...I'm no coding expert, but I should understand enough not to be missing something that has to be this simple....

I'm going to end up expanding the code and making it much more complicated later, but the problem I'm having here is in getting the MsgBox ("Not 5") to function. It's merely a test MsgBox to indicate that my else staement is triggering. It doesn't appear to be. And for the life of me, I cant figure out why....

VBA Code:
'Option Explicit requires me to define my variables
Option Explicit
'Define the variables globally
Dim LR As Long, LR2 As Long
Dim Target As Range

Private Sub Worksheet_Change(ByVal Target As Range)
    LR = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    LR2 = Target.Row
    Application.EnableEvents = False
    Application.ScreenUpdating = False
       If Not Intersect(Target, Range("A5:A" & LR)) Is Nothing Then
            If Target.Value <> "" Then
                Worksheets("Entry Page").Range("C" & LR2).Formula = _
                "...long irrelevant formula that works fine..."
            Else
                If Target.Row = 5 Then
                    MsgBox ("5")
                Else
                    MsgBox ("Not 5")
                End If
            End If
        Else
        End If

    Application.EnableEvents = True
    Application.ScreenUpdating = True
        
End Sub
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Just follow the execution in debug step-by-step mode hitting F8 key to see where your logic fails …​
 
Upvote 0
Its probably here:

VBA Code:
LR = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

If you clear the last cell in your range then the intersect will be nothing.
 
Upvote 0
untested but try this update to your code & see if does what you want

VBA Code:
'Define the variables globally
    Dim LR          As Long, LR2 As Long

Private Sub Worksheet_Change(ByVal Target As Range)

    LR = Me.Cells(Rows.Count, 1).End(xlUp).Row
    LR2 = Target.Row
    Exit Sub
    On Error GoTo myerror
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    If Intersect(Target, Range("A5:A" & LR)) Is Nothing Then
        If Target.Column = 1 Then
            If Target.Row = 5 Then
                    MsgBox ("5")
                Else
                    MsgBox ("Not 5")
            End If
        End If
    Else
        Worksheets("Entry Page").Range("C" & LR2).Formula = _
                          "...long irrelevant formula that works fine..."
        
    End If
    
myerror:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub

Dave
 
Upvote 0
Its probably here:

VBA Code:
LR = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

If you clear the last cell in your range then the intersect will be nothing.
Except that if I put data down the intersect column and then delete an intermediate cell, the msgbox doesn't pop. So....pretty sure it's not related to the last cell.
 
Upvote 0
untested but try this update to your code & see if does what you want

VBA Code:
'Define the variables globally
    Dim LR          As Long, LR2 As Long

Private Sub Worksheet_Change(ByVal Target As Range)

    LR = Me.Cells(Rows.Count, 1).End(xlUp).Row
    LR2 = Target.Row
    Exit Sub
    On Error GoTo myerror
    Application.EnableEvents = False
    Application.ScreenUpdating = False
   
    If Intersect(Target, Range("A5:A" & LR)) Is Nothing Then
        If Target.Column = 1 Then
            If Target.Row = 5 Then
                    MsgBox ("5")
                Else
                    MsgBox ("Not 5")
            End If
        End If
    Else
        Worksheets("Entry Page").Range("C" & LR2).Formula = _
                          "...long irrelevant formula that works fine..."
       
    End If
   
myerror:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
   
End Sub

Dave
I just went in and replaced it all without trying to understand it to start off with, and the results ended up in no msgboxes procc'ing and my formula not being inserted into the cells anymore, so, something awry.

I'm kind of learning the syntax, properties and methods as I need them. So the whole Exit Sub, OnError GoTo bit is new to me....going to go read up on it. But otherwise reverting back to my previous code for now.

I'm not looking to catch errors at this point, just make the fundamental concept work. :)
 
Upvote 0
I just went in and replaced it all without trying to understand it to start off with, and the results ended up in no msgboxes procc'ing and my formula not being inserted into the cells anymore, so, something awry.

I'm kind of learning the syntax, properties and methods as I need them. So the whole Exit Sub, OnError GoTo bit is new to me....going to go read up on it. But otherwise reverting back to my previous code for now.

I'm not looking to catch errors at this point, just make the fundamental concept work. :)
Ok, second update. I reverted all my code back except the "LR = Me.Cells(Rows.Count, 1).End(xlUp).Row line" and now it's all working properly.

If you have any insight as to why that change alone fixed it, I'd love to understand.
 
Upvote 0
Clear the last cell. It wont work using that LR code as i say the intersect will be nothing. It probably stopped working because you broke the code without enabling events although thats a guess.
 
Upvote 0
Consider this instead of using that LR.

VBA Code:
Dim rng As Range, c As Range

Set rng = Intersect(Target, Range("A5:A" & Rows.Count))

If Not rng Is Nothing Then
        For Each c In rng
'etc etc etc
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top