VBA how to change value in a visible cell if two criteria is met

Question

I need to change the level of each student if they passed two criteria. I wish to change the value inside the level column, but if it's not possible maybe put the changes next to the column is still accepted.

NIP	Name	Math Score	English Score	Level
1234	Ariana	75	75	Level 1
1235	Brian	80	85	Level 2
1236	Charlie	75		Level 3

Criteria 1 = Math Score; Criteria 2 = English Score

If both criteria cells is filled with values (regardless what's the score), then the student will be promoted to the next level.

Example Ariana will be Level 2 and Brian will be Level 3

While, Charlie's "D" column is not a subject to changes because criteria 1 and 2 are not met.

note: I want the code to apply on visible cells only because I'll use filter on the level column

Answer 1

you could use SpecialCells() method of Range object to filter visible and not empty cells in cascade

you should then use a lot of nested If ... Then ... and check if there are any visible rows with not empty cells in columns 3 and 4

but the conscious use of On Error Resume Next statement could simplify the matter

Sub UpDateLevel()
    With Range("A1").CurrentRegion
        With .Resize(.Rows.Count - 1).Offset(1)
            On Error Resume Next
                Dim cel As Range
                    For Each cel In Intersect(.Columns(3).SpecialCells(XlCellType.xlCellTypeVisible).SpecialCells(XlCellType.xlCellTypeConstants).EntireRow, _
                                              .Columns(4).SpecialCells(XlCellType.xlCellTypeConstants))
                        With cel.Offset(, 1)
                            .Value = Split(.Value, " ")(0) & " " & Split(.Value, " ")(1) + 1
                        End With
                    Next
        End With
    End With
End Sub

Answer 2

Assuming your table is in sheet 1 range as of A1:

    Option Explicit

    Sub update()
        Dim arr, r As Long, arr2() As String
        arr = Sheet1.Range("A1").CurrentRegion
        
        For r = 2 To UBound(arr)
            If Not IsEmpty(arr(r, 3)) And Not IsEmpty(arr(r, 4)) Then
                arr2 = Split(arr(r, 5), " ", -1, vbTextCompare)
                arr(r, 5) = arr2(0) & " " & arr2(1) + 1
            End If
        Next r
        
        Sheet1.Range("A1").CurrentRegion = arr
    End Sub

You can add a button or add an onsheetchange event to launch the code.

Answer 3

This is a little brut force but it works.