Date/Time Stamp - Macro Merge Assistance

[DaRTH KiRo]

I need assistance merging the two macros below together. The goal is to have a date/time modified displayed in column K when data is entered in column J, as well as a date/time modified displayed in column M when data is entered in column L. I already have to two setup independent but am unsure how to make them work together.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("J:J"), Target)
xOffsetColumn = 1
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng
If Not VBA.IsEmpty(Rng.Value) Then
Rng.Offset(0, xOffsetColumn).Value = Now
Rng.Offset(0, xOffsetColumn).NumberFormat = "mm-dd-yyyy, hh:mm:ss"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("L:L"), Target)
xOffsetColumn = 1
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng
If Not VBA.IsEmpty(Rng.Value) Then
Rng.Offset(0, xOffsetColumn).Value = Now
Rng.Offset(0, xOffsetColumn).NumberFormat = "mm-dd-yyyy, hh:mm:ss"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If
End Sub

 

[Joe4]

Welcome to the Board!

Since it seems that both procedures are doing the same thing, just on different ranges, try this:

Private Sub Worksheet_Change(ByVal Target As Range)

[COLOR=#ff0000]    Dim WorkRng1 As Range
    Dim WorkRng2 As Range[/COLOR]
    Dim WorkRng As Range
    Dim Rng As Range
    Dim xOffsetColumn As Integer
    
[COLOR=#ff0000]    Set WorkRng1 = Intersect(Application.ActiveSheet.Range("J:J"), Target)
    Set WorkRng2 = Intersect(Application.ActiveSheet.Range("L:L"), Target)
    
    Set WorkRng = Application.Union(WorkRng1, WorkRng2)[/COLOR]
    
    xOffsetColumn = 1
    
    If Not WorkRng Is Nothing Then
        Application.EnableEvents = False
        For Each Rng In WorkRng
            If Not VBA.IsEmpty(Rng.Value) Then
                Rng.Offset(0, xOffsetColumn).Value = Now
                Rng.Offset(0, xOffsetColumn).NumberFormat = "mm-dd-yyyy, hh:mm:ss"
            Else
                Rng.Offset(0, xOffsetColumn).ClearContents
            End If
        Next
        Application.EnableEvents = True
    End If
    
End Sub

 

[DaRTH KiRo]

Thanks for your reply! This makes sense, we were thinking we wouldn't need it twice. However, after running this we receive a run-time error 5, invalid procedure call or argument. The issue appears to be stemming from the following line (this is what it highlights when I click Debug).

Set WorkRng = Application.Union(WorkRng1, WorkRng2)

 

[Joe4]

I see, it doesn't like to use empty ranges in the Union command. So we will need to check it like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim WorkRng1 As Range
    Dim WorkRng2 As Range
    Dim WorkRng As Range
    Dim Rng As Range
    Dim xOffsetColumn As Integer
    
    Set WorkRng1 = Intersect(Application.ActiveSheet.Range("J:J"), Target)
    Set WorkRng2 = Intersect(Application.ActiveSheet.Range("L:L"), Target)
    
    If Not WorkRng1 Is Nothing Then
        If Not WorkRng2 Is Nothing Then
            Set WorkRng = Application.Union(WorkRng1, WorkRng2)
        Else
            Set WorkRng = WorkRng1
        End If
    Else
        If Not WorkRng2 Is Nothing Then
            Set WorkRng = WorkRng2
        End If
    End If
    
    xOffsetColumn = 1
    
    If Not WorkRng Is Nothing Then
        Application.EnableEvents = False
        For Each Rng In WorkRng
            If Not VBA.IsEmpty(Rng.Value) Then
                Rng.Offset(0, xOffsetColumn).Value = Now
                Rng.Offset(0, xOffsetColumn).NumberFormat = "mm-dd-yyyy, hh:mm:ss"
            Else
                Rng.Offset(0, xOffsetColumn).ClearContents
            End If
        Next
        Application.EnableEvents = True
    End If
    
End Sub

 

[DaRTH KiRo]

That worked! Thank you so much for your help!

 

[Joe4]

You are welcome!