Loop through range

2
votes

I have a cell that has a pass or fail validation (BA). If the user selects Pass from the drop down list, unit passed all tests is displayed in another cell (N). The VBA I have used to make this happen works as expected:

On Worksheet_Change 

If Range("BA17").Value = "Pass" Then Range("N44").Value = "Unit passed all tests."
If Range("BA18").Value = "Pass" Then Range("N45").Value = "Unit passed all tests."
If Range("BA19").Value = "Pass" Then Range("N46").Value = "Unit passed all tests."

Is it possible to condense this, as the final sheet will have up to 25-30 lines?

I tried adding a range, i.e. ("BA17:BA40") but then this updates all fields every time, which is not ideal.

2
vbaexcel

2 Answers

1
votes

This will update Column N (row offset by 27) with value "Unit passed all tests." if only one cell in column BA is updated and current row > 17

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    With Target
        If .CountLarge = 1 And .Column = Range("BA1").Column And .Row > 17 Then
            If .Value2 = "Pass" Then
                Application.EnableEvents = False
                .Offset(27, Range("N1").Column - .Column).Value = "Unit passed all tests."
                Application.EnableEvents = True
            End If
        End If
    End With

End Sub
0
votes

An option that avoids loops for multiple entries

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng1 As Range
Set rng1 = Intersect(Target, [BA17:BA40])
If rng1 Is Nothing Then Exit Sub

Application.EnableEvents = False
With rng1.Offset(27, -39)
        .FormulaR1C1 = "=IF(R[-27]C[39]=""Pass"",""Unit passed all tests."","""")"
        .Value = .Value
End With
Application.EnableEvents = True

End Sub