If Then Else Not Executing Else

drhill78

New Member
Joined
Feb 21, 2019
Messages
7
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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,237
Office Version
  1. 2010
Platform
  1. Windows
Just follow the execution in debug step-by-step mode hitting F8 key to see where your logic fails …​
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,601
Office Version
  1. 365
Platform
  1. Windows
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.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,790
Office Version
  1. 2019
Platform
  1. Windows
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
 

drhill78

New Member
Joined
Feb 21, 2019
Messages
7

ADVERTISEMENT

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.
 

drhill78

New Member
Joined
Feb 21, 2019
Messages
7
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. :)
 

drhill78

New Member
Joined
Feb 21, 2019
Messages
7

ADVERTISEMENT

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.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,601
Office Version
  1. 365
Platform
  1. Windows
Your original code works perfectly well for me. Except what ive already told you.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,601
Office Version
  1. 365
Platform
  1. Windows
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.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,601
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,140,996
Messages
5,703,623
Members
421,306
Latest member
ambuj Thakur

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
Top