Todays date or static

[bd1982]

hello

I am trying to see if I can have column N populate todays date if column A is populated but not if column M is also populated. I have searched and found two VBA instructions which do one or the other but being (very) new to VBA I dont know how to make them work together, or even whether they are correct. Below is an example of my table, what I want is for the outstanding date to be todays date if requisition number is present but to not change if status shows as closed, this would allow me to then calculate the days a position has been open for.


[TABLE="width: 486"]
<colgroup><col width="138" style="width: 104pt; mso-width-source: userset; mso-width-alt: 5046;"> <col width="123" style="width: 92pt; mso-width-source: userset; mso-width-alt: 4498;"> <col width="98" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3584;"> <col width="64" style="width: 48pt;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;" span="3"> <tbody>[TR]
[TD="width: 138, bgcolor: gray"]Requisition Number [/TD]
[TD="width: 123, bgcolor: gray"]Requisition Date [/TD]
[TD="width: 98, bgcolor: gray"]Outstanding Vacancies [/TD]
[TD="width: 64, bgcolor: gray"]Status [/TD]
[TD="width: 75, bgcolor: gray"]Outstanding Date[/TD]
[TD="width: 75, bgcolor: gray"]Closed Date[/TD]
[TD="width: 75, bgcolor: gray"]days open[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]110[/TD]
[TD="bgcolor: transparent"]10/01/2018[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]closed[/TD]
[TD="bgcolor: transparent, align: right"]18/10/2018[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]281
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]111[/TD]
[TD="bgcolor: transparent"]07/06/2018[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]18/10/2018[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]133[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]112[/TD]
[TD="bgcolor: transparent"]07/06/2018[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]18/10/2018[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]133[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]113[/TD]
[TD="bgcolor: transparent"]11/02/2018[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]closed[/TD]
[TD="bgcolor: transparent, align: right"]18/10/2018[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]249[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]114[/TD]
[TD="bgcolor: transparent"]06/06/2018[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]18/10/2018[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]134[/TD]
[/TR]
</tbody>[/TABLE]

The code I have is below, (appologies this is all very new to me)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A As Range, B As Range, Inte As Range, r As Range
    Set A = Range("A:A")
    Set Inte = Intersect(A, Target)
    If Inte Is Nothing Then Exit Sub
    Application.EnableEvents = False
        For Each r In Inte
            r.Offset(0, 13).Value = Date
        Next r
    Application.EnableEvents = True
    
    Set A = Range("m:m")
    Set Inte = Intersect(A, Target)
    If Inte Is Nothing Then Exit Sub
    Application.EnableEvents = False
        For Each r In Inte
            r.Offset(0, 1).Value = Date
        Next r
    Application.EnableEvents = True
End Sub

If someone could point me in the right direction that would be great.

Thanks in advance

 

[yky]

Would you elaborate on this "if requisition number is present"?

Seems to me you want something like this:

If status is not "closed" and requisition number is present then
set outstanding date to today's date
End If

 

[bd1982]

hi

essentially I want to be able to start tracking the days a post has been outstanding from the time the requisition number is added to the time the status is set to closed so what you have put would be right if the date "closed" was added remained static

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("A:A,M:M")) Is Nothing Then
      If Target.Column = 1 Then
         Target.Offset(, 13).Formula = "=today()"
      ElseIf Target.Column = 13 Then
         If LCase(Target.Value) = "closed" Then Target.Offset(, 1).Value = Date
      End If
   End If
End Sub

 

[bd1982]

Thanks, I'll try this and see if it sticks when I update the tacker tomorrow and let you know.

much appreciated.

 

[bd1982]

Thank you, this appears to have worked.

 

[Fluff]

Glad to help & thanks for the feedback