none
VBA / Visual Basic Excel code - Skip rule if cell contains value RRS feed

  • Question


  • I have written the following VBA code to automatically populate column "P" with "pending", "Not due","-" based on the value of column "O".

    The user will manually have to enter "complete" into column "P" if the task is done. This is not auto populated.

    I need to make sure that last rule (if column O is "yes", then enter "pending into column P) is skipped if column "P" already says "Complete".

    Can anyone help me please? Fairly urgent. Thanks!




    Sub info()

    Dim i As Long

    For i = 11 To ActiveSheet.Cells(Rows.Count, 11).End(xlUp).row

        If ActiveSheet.Cells(i, 15) = "No" Then

            ActiveSheet.Range("P" & i) = "Not due"

        End If

    Next i

    For i = 11 To ActiveSheet.Cells(Rows.Count, 11).End(xlUp).row
        If ActiveSheet.Cells(i, 15) = "-" Then
            ActiveSheet.Range("P" & i) = "-"
       
     End If

    Next i

    **For i = 11 To ActiveSheet.Cells(Rows.Count, 11).End(xlUp).row

        If ActiveSheet.Cells(i, 15) = "Yes" Then

            ActiveSheet.Range("P" & i) = "Pending"

        End If**

    Next i

    End Sub
    Monday, January 15, 2018 11:07 AM

All replies

  • Like this:

    Sub info()
        Dim i As Long
        For i = 11 To ActiveSheet.Cells(Rows.Count, 11).End(xlUp).Row
            Select Case ActiveSheet.Cells(i, 15).Value
                Case "No"
                    ActiveSheet.Cells(i, 16).Value = "Not due"
                Case "-"
                    ActiveSheet.Cells(i, 16).Value = "-"
                Case "Yes"
                    If ActiveSheet.Cells(i, 16).Value <> "Complete" Then
                        ActiveSheet.Cells(i, 16).Value = "Pending"
                    End If
            End Select
        Next i
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, January 15, 2018 1:38 PM