vendredi 23 septembre 2016

Nested IF Statement with an autopopulate function

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A As Range, B As Range, Inte As Range, r As Range
    Set A = Range("E:E,J:J,N:N,R:R,U:U,X:X,AF:AF,AJ:AJ,AM:AM,AP:AP,AT:AT,AW:AW,AZ:AZ,BC:BC,BG:BG,BJ:BJ")
    Set Inte = Intersect(A, Target)
    If Inte Is Nothing Then Exit Sub
    Application.EnableEvents = False
     For Each r In Inte
        If ((r.Value > 0) And (r.Offset = "")) Then
             r.Offset(0, 1).Value = Date
             r.Offset(0, 1).NumberFormat = "dd-mm-yyyy"
             r.Offset(0, 2).Value = Time
             r.Offset(0, 2).NumberFormat = "hh:mm:ss AM/PM"
        Else
           r.Offset(0, 1).Value = ""
           r.Offset(0, 2).Value = ""
        End If
    Next r
    Application.EnableEvents = True
    End Sub

Above is the code I am using (credit: Adnan Shahid) . I am having trouble with a nested If statement specifically the (r.Offset = ""). The end game is that when a value is entered in the specified ranges the date and time auto-populate. However, I would also like to make it so that if a user makes a mistakes and changes the value in the range the initial date and time given does not change.

Aucun commentaire:

Enregistrer un commentaire