• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Autodate change

rbobcat1

Member
New to VBA and I cannot seem to get this to work.
i am pretty sure it has to do with the first line
If i enter something in column C i want it to put the date in D.
Also when I change column F to 'Closed' I want it to put a date in G.

>>> use code - tags <<<

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
' Auto Date
Dim Cell As Range
For Each Cell In Target
    If Cell.Column = Range("C:C").Column Then
        If Cell.Value <> "" Then
            Cells(Cell.Row, "D").Value = Now
        Else
            Cells(Cell.Row, "D").Value = ""
        End If
    End If
Next Cell
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
' Auto Date
Dim Cell As Range
For Each Cell In Target
    If Cell.Column = Range("f:f").Column Then
        If Cell.Value = "closed" Then
            Cells(Cell.Row, "g").Value = Now
        Else
            Cells(Cell.Row, "g").Value = ""
        End If
    End If
Next Cell
End Sub
 
Last edited by a moderator:
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' Auto Date
Dim Cell As Range
For Each Cell In Target
    If Cell.Column = Range("C:C").Column Then
        If Cell.Value <> "" Then
        Cells(Cell.Row, "D").Value = Now
        End If
    End If
Next Cell

' Auto Date
    For Each Cell In Target
        If Cell.Column = Range("f:f").Column Then
            If Cell.Value = "closed" Then
                Cells(Cell.Row, "g").Value = Now
            End If
        End If
    Next Cell
End Sub
 
OPTION EXPLICIT makes you declare your variables. Ex: Dim Cell as Range, Dim i As Long or Dim i As Integer. Those are some examples.

It also auto checks your code for errors. Not all of them but just about. That way, so long as you are not using "On Error Resume Next" or
"On Error GoTo 0" in the top of your code, Excel will halt the execution of your code, provide a code error for you to work with.

Other than that, combining the two macros into one (you never want to repeat a macro name as it causes an error. That is what you did
in your original code). Put all your code that needs to go into a macro, don't repeat it. Each macro is designed to do one thing. In this case
it was meant to add a date in two different location based on input.

You should be good to go.
 
ps: Let me correct something just to be completely honest.

You COULD put all of your code in one macro, so long as clicking one button is all you want to do and everything in that one macro is all you want to get accomplished.

HOWEVER, most projects are broken down into multiple macros. It is easier to trouble shoot later ... easier to read to understand what is happening with the code. AND ... most projects can't have all the code in one macro because everything gets done with one button click. Most all projects
have numerous actions that are needing to be completed but they are all separate from one another. For example : You want to copy something and paste it somewhere. BUT that is all you want to do. That is one macro. Or, you want to print something BUT that is all you want to do. That is another macro by itself.
 
As only an unique loop is necessary :​
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
         Dim Rg As Range
         Application.EnableEvents = False
    For Each Rg In Target
        Select Case Rg.Column
               Case 3:  Rg(1, 2) = IIf(IsEmpty(Rg), Empty, Date)
               Case 6:  Rg(1, 2) = IIf(Rg.Value2 = "Closed", Date, Empty)
        End Select
    Next
         Application.EnableEvents = True
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Back
Top