mardi 21 juin 2016

many conditions using IF code

I'm having trouble on building a multiple condition code. Here's what I need to do:

these conditions must run down trough a list.

  • When colum H already has a date, do nothing to colum H;
  • When AW (date colums) is empty do nothing to colum H;
  • When colum J is writen "Date closed" do nothing to colum H;
  • when colum I is writen "Control", cell in colum H equals to cell in colum AW
  • When colum Z is writen "Transport to STK/FORN" insert date 2 days in front of today in colum H
  • If none of the above then cell in H = cell in AW
Sub Macro1()

Dim r As Range
Dim n As Long

Range("A3").Select
Do Until IsEmpty(ActiveCell)

On Error Resume Next

Set r = Range("myRange")
For n = 1 To r.Rows.Count

        If r.Cells(n, 8) <> "" & r.Cells(n, 49) = "" & r.Cells(n, 10) = "Closed Date" Then

    Worksheets("plan1").Cells.Locked = False
    Worksheets("plan1").r.Cells(n, 8).Lock = True
    Worksheets("plan1").Protect Password:="msa", UserInterfaceOnly:=True

    End If

        If r.Cells(n, 9) = "Control" Then

    r.Cells(n, 8) = Range(r.Cells(n, 49)).Value


        End If


        If r.Cells(n, 26) = "Transport to STK/FORN" Then

    r.Cells(n, 26) = DateAdd("w", 2, Date)

    Else
        r.Cells(n, 8) = Range(r.Cells(n, 49)).Value
End If

Next n

ActiveCell.Offset(1, 0).Select
Loop

End Sub

I tried to lock the cells in the first condition so the conditions below won't overwrite these first conditions.

Aucun commentaire:

Enregistrer un commentaire