Why is If, Then VBA statement not executing?

Hello Experts,
I am not able to get the following code to execute when either cell, J2 or K2 values change.  I am not receiving any errors, just no execution.  Any ideas why?


Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rngTerm1 As Range, rngTerm2 As Range, rngUnion As Range
       
       
    ' The variable rngUnion contains the cells that, if either is changed, will
    ' update the record count for retained students according to the term specified.
    Set rngTerm1 = Range("J2")
    Set rngTerm2 = Range("K2")
    Set rngUnion = Union(Range("J2"), Range("K2"))

    If Not Application.Intersect(rngUnion, Range(Target.Address)) Is Nothing Then
    
        'for changing the formulas to reflect fall to fall terms
        If rngTerm1 = 10 And rngTerm2 = 10 Then
            
    Range("H23:H118").Select
        Selection.Replace What:="SPRING", Replacement:="FALL", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

        
        'for changing the formulas to reflect spring to spring terms
        ElseIf rngTerm1 = 20 And rngTerm2 = 20 Then
            
    Range("H23:H118").Select
        Selection.Replace What:="FALL", Replacement:="SPRING", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

          
        'for changing the formulas to reflect spring to fall terms
        ElseIf rngTerm1 = 20 And rngTerm2 = 10 Then
            
            
    Range("H23:H30,H39:H46,H55:H62,H71:H78,H87:H94,H103:H110").Select
        Selection.Replace What:="SPRING", Replacement:="FALL", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
    Range("H26,H30,H42,H46,H58,H62,H74,H78,H90,H94,H106,H110").Select
        Selection.Replace What:="GRADUATION GROUP", Replacement:="GRADUATION - FALL GROUP", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Range("H26,H30,H42,H46,H58,H62,H74,H78,H90,H94,H106,H110").Select
        Selection.Replace What:="GRADUATION - SPRING GROUP", Replacement:="GRADUATION - FALL GROUP", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Range("H31:H38,H47:H54,H63:H70,H79:H86,H95:H102,H111:H118").Select
        Selection.Replace What:="FALL", Replacement:="SPRING", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
    Range("H34,H38,H50,H54,H66,H70,H82,H86,H98,H102,H114,H118").Select
        Selection.Replace What:="GRADUATION GROUP", Replacement:="GRADUATION - SPRING GROUP", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Range("H34,H38,H50,H54,H66,H70,H82,H86,H98,H102,H114,H118").Select
        Selection.Replace What:="GRADUATION - FALL GROUP", Replacement:="GRADUATION - SPRING GROUP", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    
                       
        'for changing the formulas to reflect fall to spring terms
        ElseIf rngTerm1 = 10 And rngTerm2 = 20 Then
            
    Range("H23:H30,H39:H46,H55:H62,H71:H78,H87:H94,H103:H110").Select
        Selection.Replace What:="FALL", Replacement:="SPRING", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Range("H26,H30,H42,H46,H58,H62,H74,H78,H90,H94,H106,H110").Select
        Selection.Replace What:="GRADUATION GROUP", Replacement:="GRADUATION - SPRING GROUP", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Range("H26,H30,H42,H46,H58,H62,H74,H78,H90,H94,H106,H110").Select
        Selection.Replace What:="GRADUATION - FALL GROUP", Replacement:="GRADUATION - SPRING GROUP", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Range("H31:H38,H47:H54,H63:H70,H79:H86,H95:H102,H111:H118").Select
        Selection.Replace What:="SPRING", Replacement:="FALL", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
    Range("H34,H38,H50,H54,H66,H70,H82,H86,H98,H102,H114,H118").Select
        Selection.Replace What:="GRADUATION GROUP", Replacement:="GRADUATION - FALL GROUP", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Range("H34,H38,H50,H54,H66,H70,H82,H86,H98,H102,H114,H118").Select
        Selection.Replace What:="GRADUATION - SPRING GROUP", Replacement:="GRADUATION - FALL GROUP", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
           
        Else
                End If
            End If
        End Sub

Open in new window


Many Thanks in Advance,

Lindsay
Lindsay_KAsked:
Who is Participating?
 
Saurabh Singh TeotiaCommented:
The code works for me without any problem..I'm assuming you have this code in the sheet module only...And of the correct sheet where you are working..

Even after that if it doesn't work that means you don't have events enable in excel..Just enable them by running the below mentioned code...

Sub X()
        Application.EnableEvents = True
        
        End Sub

Open in new window


What this do will enable events in your excel which should make this code trigger once you make any changes in your cell

Saurabh...
0
 
Martin LissOlder than dirtCommented:
Providing that the event is actually triggered (and if not then Saurabh's suggestion will fix that), you may find the techniques described in my article on debugging useful in finding the problem.

Oh, and note that the change event isn't fired until you actually leave the cell.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
On another note, you should contract
 Range("H34,H38,H50,H54,H66,H70,H82,H86,H98,H102,H114,H118").Select
        Selection.Replace '...

Open in new window

to something like
 Range("H34,H38,H50,H54,H66,H70,H82,H86,H98,H102,H114,H118").Replace ' ...

Open in new window

as using selections is prone to errors, and changes the current selection even if that intercepts with user interaction. And first selecting the ranges you want to work on in VBA is unnecessary.
And if you wish to apply several operation to the same range, WITH is your friend:
with  Range("H34,H38,H50,H54,H66,H70,H82,H86,H98,H102,H114,H118")
   .Replace '...
   .Replace '...
end with

Open in new window

0
 
Lindsay_KAuthor Commented:
Thanks so much to all of you,
I got it going again with the incorporation of Saurabh and Qlemo's solutions, along with some fixes in my formulas.  I believe the Application.EnableEvents = True was the fundamental fix, with Qlemo's solution allowing the code to run immediately instead of getting hung up and appearing not to execute.

Many thanks again!

-Lindsay
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.