Beggininer Excel VBA TimeStamp Issue

[alaskaexcel]

Hi,

I am new to using VBA and have created the following by grabbing bits and pieces from the internet to make this work. Basically, I have a status column (G) with drop downs, and when I drop down and select certain values, columns are automatically updated with todays date.

However, I would like to be able to manually change the date in the automated columns producing dates, but I receive error:

"-2147417848 (80010108)
Method 'Range' of Object_Worksheet Failed"

I am able to change the date for column H (the first offset), but i cannot change the dates for any of the other columns offets 3-6, & 9. I just get the error above and it points to the row of code highlighted RED below.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim WorkRng As Range
Dim Rng As Range
Dim Rng2 As Range
Dim xOffsetColumn As Integer
Set WorkRng =Intersect(Application.ActiveSheet.Range("G:G"), Target)
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng InWorkRng
If NotVBA.IsEmpty(Rng.Value) Then
Rng.Offset(0, 1).Value = Date
Rng.Offset(0, 1).NumberFormat = "mm/dd/yyyy"
Else
Rng.Offset(0, 1).ClearContents
End If
Next
End If
Set Status = ActiveSheet.Range("G:G")
For Each Rng2 In Status
If Rng2.Value = "CV - Review" Then
Rng2.Offset(0,2).Value = Date
Rng2.Offset(0,2).NumberFormat = "mm/dd/yyyy"
Else

If Rng2.Value = "In - R0" Then
Rng2.Offset(0,3).Value = Date
Rng2.Offset(0,3).NumberFormat = "mm/dd/yyyy"
Else
If Rng2.Value = "In - R1" Then
Rng2.Offset(0,4).Value = Date
Rng2.Offset(0,4).NumberFormat = "mm/dd/yyyy"
Else
If Rng2.Value = "In - R2" Then
Rng2.Offset(0,5).Value = Date
Rng2.Offset(0,5).NumberFormat = "mm/dd/yyyy"
Else
If Rng2.Value = "In - R3" Then
Rng2.Offset(0,6).Value = Date
Rng2.Offset(0,6).NumberFormat = "mm/dd/yyyy"
Else
If Rng2.Value = "Accepted" Then
Rng2.Offset(0,9).Value = Date
Rng2.Offset(0,9).NumberFormat = "mm/dd/yyyy"


End If
End If
End If
End If
End If
End If
Next
Application.EnableEvents = True
End Sub

 

[AlphaFrog]

I'm not sure I can deduce exactly what you are trying to do from just your code, but give this a try.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim WorkRng   As Range
    Dim Rng       As Range
    Dim os        As Long
    
    Set WorkRng = Intersect(Application.ActiveSheet.Range("G:G"), Target)
    
    If Not WorkRng Is Nothing Then
        Application.EnableEvents = False
        For Each Rng In WorkRng
            If Not IsEmpty(Rng) Then
                Rng.Offset(0, 1).Value = Date
                Rng.Offset(0, 1).NumberFormat = "mm/dd/yyyy"
            Else
                Rng.Offset(0, 1).ClearContents
            End If
            
            Select Case Rng.Value
                Case "CV - Review": os = 2
                Case "In - R0": os = 3
                Case "In - R1": os = 4
                Case "In - R2": os = 5
                Case "In - R3": os = 6
                Case "Accepted": os = 9
                Case Else: os = 0
            End Select
            If os > 0 Then
                Rng.Offset(0, os).Value = Date
                Rng.Offset(0, os).NumberFormat = "mm/dd/yyyy"
            End If
            
        Next
    End If


    Application.EnableEvents = True
End Sub

 

[alaskaexcel]

Thank you so much! This worked perfectly! Curious, if I wanted to return a String value based on a select case, what would I do?

 

[AlphaFrog]

You're welcome.

A nonspecific answer to a nonspecific question...

            Dim MyString as String


            Select Case Rng.Value
                Case "CV - Review": MyString = "A"
                Case "In - R0": MyString = "B"
                Case "In - R1": MyString = "B"
                Case "In - R2": MyString = "D"
                Case "In - R3": MyString = "E"
                Case "Accepted": MyString = "Z"
                Case Else: MyString = ""
            End Select