VBA calculating dates difference

[wasntme]

Hello, I am trying to write some code that will calculate the days between two dates if cell in column "A" is populated. Currently i have this bit (if A is populated, Q(offset 16) should equal the difference between N(offset 13) and P(offset 15):

Dim cell As Range

For Each cell In Range("A3:A100")
    If Not IsEmpty(cell) Then
       cell.Offset(, 16).Value = DateDiff("d", cell.Offset(, 13), cell.Offset(, 15))
    End If
Next

The problem is that it works for the first row and then excel freezes. Few things to note:
* code is in Private Sub Worksheet_Change(ByVal Target As Range)
* dates in cells i want to find difference are populated by VBA based on conditions (drop down menu), i want to be able to edit them manually (i can) and the result to auto calculate as well, not just by the condition
* i have Application.Calculation = xlCalculationAutomatic at start of the Sub

I am not very good with VBA and i have written lots of other code there, that might cause any trouble, but without this bit above, everything works fine. Can provide the whole file if needed. Thanks in advance

 

[wasntme]

Hello Marcelo,

Let me try to shed some light. Columns N and P populate with today date when the relevant status from the drop down is selected. Column Q, where i need this to work should just auto calculate the difference in days between them, the bit of code i have posted does exactly this, but it triggers only on changing statuses in N and P.

I want also to be able to manually change values in N and P with other date, as this scenario happens often, and Q to calculate and show the result with only condition there is data in A (project names).

Thanks in advance.

 

[Marcelo Branco]

See if this does what you need
(code below in sheet code module)

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, Range("N3:N100, P3:P100")) Is Nothing Then
        Application.EnableEvents = False
        If Not IsEmpty(Cells(Target.Row, "A")) Then
            Cells(Target.Row, "Q") = DateDiff("d", Cells(Target.Row, "N"), Cells(Target.Row, "P"))
        End If
        Application.EnableEvents = True
    End If
End Sub

M.

 

[wasntme]

Hello Marcelo,

This is it! Works like a charm. Thank you so much!

 

[Marcelo Branco]

You're welcome. Glad to help.

M.

 

[wasntme]

You're welcome. Glad to help.

M.

Hello again Marcelo,

I have one more question related to this topic. If you consider that it needs a separate thread, will move it.

I have a summary sheet with SUMIFS and COUNTIFS formulas with ranges the columns that are populated with VBA (those N and P columns). Those formula results don't update unless i enter the values manually (or simply activate and ENTER). Calculations are set on automatic in Excel, there is also forced application calculation at start of my code.

Thanks in advance.

 

[Marcelo Branco]

Hi

It is a different question and therefore deserves a new topic.

You should be clear as to:
What exactly do you mean by "results don't update" (which formulas)?
What expected / desired behavior does not happen?

M.