mardi 17 septembre 2019

if statments with "AND , OR"

please i need help on this code: it compares different values in different column at the same row level and executes the "then statement". But the code i wrote doesn't real function as i expected.

Sub Z_status()

Dim wsO As Worksheet
Set wsO = Sheets("Sending List")

Dim i As Long
Dim Lastrow As Long

With wsO

    Lastrow = Cells(Rows.Count, 5).End(xlUp).Row
    'Lastrow_2 = Cells(Rows.Count, 6).End(xlUp).Row
    'Lastrow_3 = Cells(Rows.Count, 3).End(xlUp).Row
    'Lastrow_4 = Cells(Rows.Count, 8).End(xlUp).Row

    For i = Lastrow To 2 Step -1
    'For j = Lastrow_2 To 2 Step -1
    'For k = Lastrow_3 To 2 Step -1
    'For l = Lastrow_3 To 2 Step -1

    Cells(1, 7).Value = "Expected state"

    If (Cells(i, 5).Value = "MTS" Or Cells(i, 5).Value = "MTO") And (Cells(i, 6).Value = "1/1/1900" Or Cells(i, 6).Value > Date) And (Cells(i, 3).Value = 0) And (Cells(i, 8).Value = 0) Then
        Cells(i, 7).Value = "Z1"
    ElseIf (Cells(i, 5).Value = "MTS" Or Cells(i, 5).Value = "MTO") And (Cells(i, 6).Value = "1/1/1900" Or Cells(i, 6).Value > Date) And (Cells(i, 3).Value = 0) And (Cells(i, 8).Value > 0 Or Cells(i, 8).Value = 0) Then
        Cells(i, 7).Value = "Z3"
    ElseIf (Cells(i, 5).Value = "MTS" Or Cells(i, 5).Value = "MTO") And (Cells(i, 6).Value = "1/1/1900" Or Cells(i, 6).Value > Date) And (Cells(i, 3).Value > 0) And (Cells(i, 8).Value > 0 Or Cells(i, 8).Value = 0) Then
        Cells(i, 7).Value = "Z5"
    ElseIf (Cells(i, 5).Value = "Obsolete") And (Cells(i, 6).Value < Date) And (Cells(i, 3).Value > 0) And (Cells(i, 8).Value > 0 Or Cells(i, 8).Value = 0) Then
        Cells(i, 7).Value = "Z7"
    ElseIf (Cells(i, 5).Value = "Obsolete") And (Cells(i, 6).Value < Date) And (Cells(i, 3).Value = 0) And (Cells(i, 8).Value = 0) Then
        Cells(i, 7).Value = "Z9"
    End If

    Next i
   ' Next j
   ' Next k
   ' Next l

End With

End Sub

Aucun commentaire:

Enregistrer un commentaire