IF function does not populate when condition met

Would like to know what is wrong with the IF function for the code below as it does not populate the expected text "waive admin fee" when condition is met..

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws          As Worksheet
    Dim i           As Long
    Dim lastRow     As Long
    
    Set ws = ThisWorkbook.Sheets("Complete (new format)")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ThisWorkbook.Activate
    
    Application.EnableEvents = FALSE
    
    On Error Resume Next
    
    For i = 2 To lastRow
        
        If ws.Range("K" & i).Value > 0 And ws.Range("K" & i).Value <= 10 Then
            
            ws.Range("L" & i).Value = "waive admin fee"
            
        End If
        
    Next i
    
    Application.EnableEvents = TRUE
    
End Sub

thanks for the assistance in advance..

excel

Formulas and Functions

Wouldn't it be easier to enter the following formula in L2 and fill down?

=IF(AND(K2>0,K2<=10),"waive admin fee","")

If you prefer the event procedure:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If Not Intersect(Range("K2:K" & Rows.Count), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rng In Intersect(Range("K2:K" & Rows.Count), Target)
            If rng.Value > 0 And rng.Value <= 10 Then
                rng.Offset(0, 1).Value = "waive admin fee"
            Else
                rng.Offset(0, 1).ClearContents
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

HansVogelaar

MVP

Oct 02, 2022

hrh_dash

This is an event procedure for the Worksheet_Change event.

Do you want it to react to changes in column K?
If so, does the user edit column K, or does column K contain formulas?

hrh_dash

Iron Contributor

Oct 04, 2022

,

1. Yes, i would like to react to changes in column K and
2. Column K does not contain formulas just data

HansVogelaar

MVP

Oct 04, 2022

hrh_dash

Wouldn't it be easier to enter the following formula in L2 and fill down?

=IF(AND(K2>0,K2<=10),"waive admin fee","")

If you prefer the event procedure:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If Not Intersect(Range("K2:K" & Rows.Count), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rng In Intersect(Range("K2:K" & Rows.Count), Target)
            If rng.Value > 0 And rng.Value <= 10 Then
                rng.Offset(0, 1).Value = "waive admin fee"
            Else
                rng.Offset(0, 1).ClearContents
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Forum Discussion

IF function does not populate when condition met

Share

Resources