Two VBAs, similar function

[Ikrion]

I have a script that once I put something in a cell in column G, it automatically drops the current date/time into the next cell in column H. It runs as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range, T As Range
Set T = Target
Set C = Range("G:G")
If Intersect(T, C) Is Nothing Then Exit Sub
If T.Offset(0, 1).Value <> "" Then Exit Sub
Application.EnableEvents = False
T.Offset(0, 1).Value = Now
Application.EnableEvents = True
End Sub


Well, now I need to do that again, on the same spreadsheet. I tried just copying and pasting the code over again, and chaing the range and the offset, but I get an "Ambiguous Name" error. I am assuming that's because the Subs have the same name, but if I change it, it doesn't work at all. What do I need to change the script to to allow me to run in for a separate, but similar operation, in a difference cell?

In this case, I want the Range to be J:J, and kick 31 cells over. At the same time, I want it to only activate if the letter dropped in is "X".

Any ideas?

 

[Scott Huish]

It all has to be combined in the same event. But please be clear about your requirements.

I get that if you enter something in column G, you want a date put in Column H of the corresponding row.

If something is put in Column J, you want a date put in Column AO?

If something is put in Column X, where does the date go? Or do you mean you only want to do the above two items when something is put in Column X?

 

[Ikrion]

1. If I put anything into column G, I want the date in column H.

2. If I put only the letter "X" into column J, I want the date in column AO.

Right now #1 works great by itself. It's #2 I can't get to work.

 

[shg]

Maybe (untested) ...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r         As Range
    Dim cell      As Range
 
    Application.EnableEvents = False
    
    Set r = Intersect(Target, Me.Columns("G"))
    If Not r Is Nothing Then
        For Each cell In r
            If Not IsEmpty(cell.Value) Then Me.Cells(cell.Row, "H").Value = Date
        Next cell
    End If
 
    Set r = Intersect(Target, Me.Columns("J"))
    If Not r Is Nothing Then
        For Each cell In r
            If LCase(cell.Value) = "x" Then Me.Cells(cell.Row, "AO").Value = Date
        Next cell
    End If
 
    Application.EnableEvents = False
End Sub

 

[Ikrion]

Hmm.... The first part of it worked, but not the second. Let me reload the sheet (lots of linked data) and try it again.

 

[Ikrion]

So, it drops the date in the first cell, but then it seems like the script stops working. Doesn't drop the date in the next cell down, etc, so it's almost like it only executes once. Is that possible?

 

[shg]

Oops. Change the last line to

Application.EnableEvents = True

... and save, close, and reopen the workbook.

 

[Ikrion]

Works flawlessly. Perfect.

Thank you so much!