VBA calculating dates difference

wasntme

New Member
Joined
Feb 1, 2019
Messages
37
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):

Code:
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
 
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.
 
Last edited:
Upvote 0

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
See if this does what you need
(code below in sheet code module)

Code:
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top