VBA Excel Problem

[MarkAR]

Hello all, I'm new here and need a bit of help please (new to VBA Excel)

I'm trying to get the following code to work but cant work out how to go from one stage to the next

Private Sub Worksheet_Change(ByVal Target As Range)

'First name entry (cell D), time & date auto entered (cell A & B)

Dim A As Range, B As Range, Inte As Range, r As Range

Set A = Range("D:D")

Set Inte = Intersect(A, Target)

If Inte Is Nothing Then Exit Sub
Application.EnableEvents = False
    For Each r In Inte
        If r.Value > 0 Then
           r.Offset(0, -3).Value = Date
           r.Offset(0, -3).NumberFormat = "dd-mm-yyyy"
           r.Offset(0, -2).Value = Time
           r.Offset(0, -2).NumberFormat = "hh:mm:ss AM/PM"
        Else
           r.Offset(0, -3).Value = ""
           r.Offset(0, -2).Value = ""
        End If
    Next r
    
  
Application.EnableEvents = True

End Sub


'Second name entry (cell I), time & date auto entered (cell G & H)

Dim G As Range, H As Range, Inte2 As Range, r2 As Range

Set G = Range("I:I")

Set Inte2 = Intersect(G, Target)


If Inte2 Is Nothing Then Exit Sub
Application.EnableEvents = False
    For Each r2 In Inte2
        If r2.Value > 0 Then
           r2.Offset(0, -1).Value = Date
           r2.Offset(0, -1).NumberFormat = "dd-mm-yyyy"
           r2.Offset(0, -2).Value = Time
           r2.Offset(0, -2).NumberFormat = "hh:mm:ss AM/PM"
        Else
           r2.Offset(0, -1).Value = ""
           r2.Offset(0, -2).Value = ""
        End If
    Next r2
    
  
Application.EnableEvents = True

End Sub

Would be very greatful of any help give........thanks in advance

Mark

 

[Joe4]

Welcome to the Board!

I'm trying to get the following code to work but cant work out how to go from one stage to the next

Please explain what you mean by this.
Maybe explain (in plain English) exactly what you want to happen.

 

[MarkAR]

Welcome to the Board!


Please explain what you mean by this.
Maybe explain (in plain English) exactly what you want to happen.

Sorry, Im trying to get the dat and time to auto fill in cell a and cell b, when name is put into cell d, then if name is put in cell I, auto date and time in cell g and h
I can get the first auto fill in cell a and b but the second part, name in cell I, I cant get cell g and h to auto fill with date and time

 

[Joe4]

Sorry, I didn't see the second part of your code (I didn't scroll down).

The issue is that the first check is telling it if it is not in column D, then exit the Sub. So it will never get to the second part.
So, you could modify those command like this:

Private Sub Worksheet_Change(ByVal Target As Range)

'First name entry (cell D), time & date auto entered (cell A & B)

Dim A As Range, B As Range, Inte As Range, r As Range

Set A = Range("D:D")

Set Inte = Intersect(A, Target)

[COLOR=#ff0000]If Not Inte Is Nothing Then[/COLOR]
    Application.EnableEvents = False
    For Each r In Inte
        If r.Value > 0 Then
           r.Offset(0, -3).Value = Date
           r.Offset(0, -3).NumberFormat = "dd-mm-yyyy"
           r.Offset(0, -2).Value = Time
           r.Offset(0, -2).NumberFormat = "hh:mm:ss AM/PM"
        Else
           r.Offset(0, -3).Value = ""
           r.Offset(0, -2).Value = ""
        End If
    Next r
    Application.EnableEvents = True
End If

'Second name entry (cell I), time & date auto entered (cell G & H)
Dim G As Range, H As Range, Inte2 As Range, r2 As Range

Set G = Range("I:I")

Set Inte2 = Intersect(G, Target)

[COLOR=#ff0000]If Not Inte2 Is Nothing Then[/COLOR]
    Application.EnableEvents = False
    For Each r2 In Inte2
        If r2.Value > 0 Then
           r2.Offset(0, -1).Value = Date
           r2.Offset(0, -1).NumberFormat = "dd-mm-yyyy"
           r2.Offset(0, -2).Value = Time
           r2.Offset(0, -2).NumberFormat = "hh:mm:ss AM/PM"
        Else
           r2.Offset(0, -1).Value = ""
           r2.Offset(0, -2).Value = ""
        End If
    Next r2
    Application.EnableEvents = True
End If

End Sub

However, this is some redundancy to your code that we can eliminate like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    Dim myCol As Long
    Dim dateOffset As Long
    Dim timeOffset As Long
    Dim chkUpdate As Boolean
    
    For Each cell In Target
        chkUpdate = False
        Select Case cell.Column
'           Update if column D
            Case 4
                chkUpdate = True
                dateOffset = -3
                timeOffset = -2
'           Update if column I
            Case 9
                chkUpdate = True
                dateOffset = -2
                timeOffset = -1
        End Select
'       Make updates
        If chkUpdate Then
            Application.EnableEvents = False
            If cell.Value > 0 Then
                cell.Offset(0, dateOffset).Value = Date
                cell.Offset(0, dateOffset).NumberFormat = "dd-mm-yyyy"
                cell.Offset(0, timeOffset).Value = Time
                cell.Offset(0, timeOffset).NumberFormat = "hh:mm:ss AM/PM"
            Else
                cell.Offset(0, dateOffset).Value = ""
                cell.Offset(0, timeOffset).Value = ""
            End If
            Application.EnableEvents = True
        End If
    Next cell
                
End Sub

 

[MarkAR]

Thank you so much, i will try this out, again many thanks

 

[Joe4]

You are welcome.