Adding Else into an If statement

[kchm_2000]

Hi,

I have a formula that currently replys OK if it captures "C to BBNT" or "Will C SD" on Col D. However I want to add another line that gives me "NOT OK" it it is anything else. Thanks for anyones' help in advance

Public Sub NT_comment()
Dim c_ell As Range, Date_Ref As Date, S_heet As Worksheet, c_ell2 As Range

For Each S_heet In ActiveWorkbook.Sheets
If Left(S_heet.Name, 3) = "ACD" Then
    S_heet.Select
    For Each c_ell In Range("D2", Cells(Rows.Count, 2).End(xlUp))
            If c_ell = "C to BBNT" Then
                c_ell.Offset(0, 1) = "OK"
            If c_ell = "Will C SD" Then
                c_ell.Offset(0, 1) = "OK"
            End If
    Next c_ell
End If
Next S_heet
End Sub

[TMS]

One way:

Public Sub NT_comment()
Dim c_ell As Range, Date_Ref As Date, S_heet As Worksheet, c_ell2 As Range

For Each S_heet In ActiveWorkbook.Sheets
    If Left(S_heet.Name, 3) = "ACD" Then
        S_heet.Select
        For Each c_ell In Range("D2", Cells(Rows.Count, 4).End(xlUp))
            Select Case c_ell.Value
                Case "C to BBNT": c_ell.Offset(0, 1) = "OK"
                Case "Will C SD": c_ell.Offset(0, 1) = "OK"
                Case Else: c_ell.Offset(0, 1) = "Not OK"
            End Select
        Next c_ell
    End If
Next S_heet
End Sub

Think you were specifying the wrong column [Cells(Rows.Count, 4).End(xlUp))]


Regards

[Marcol]

Try this

Public Sub NT_comment()
    Dim c_ell As Range, Date_Ref As Date, S_heet As Worksheet, c_ell2 As Range

    For Each S_heet In ActiveWorkbook.Sheets
        If Left(S_heet.Name, 3) = "ACD" Then
            With S_heet
                For Each c_ell In .Range("D2:D" & .Range("D" & .Rows.Count).End(xlUp).Row)
                    If c_ell = "C to BBNT" Or c_ell = "Will C SD" Then
                        c_ell.Offset(0, 1) = "OK"
                    Else
                        If c_ell <> "" Then c_ell.Offset(0, 1) = "Not OK"
                    End If
                Next c_ell
            End With
        End If
    Next S_heet
End Sub

[snb]

Sub snb()
 For Each sh In Sheets
   If Left(sh.Name, 3) = "ACD" Then
    For Each cl In sh.Cells(2, 4).Resize(sh.Cells(Rows.Count, 2).End(xlUp).Row - 1)
     cl.Offset(, 1).Value = IIf(cl = "C to BBNT" Or cl = "Will C SD", "", "Not ") & "OK"
    Next
   End If
 Next
End Sub

[kchm_2000]

thx very much the codes work well

[TMS]

You're welcome.