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

 

[Marcelo Branco]

Put your code in a standard module, not in the sheet module, that is:
Alt+F11 to open the VBEditor
Menu
Insert > Module

Assuming data in Sheet1, paste in the right panel

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

M.

 

[wasntme]

Hello Marcelo,

When i put it in module i get "Run time error '9'. Subscript out of range" in

For Each cell In Sheets("Sheet1").Range("A3:A100")

. The reason i have put everything in Worksheet_Change sub is that everything is triggered by values from 2 drop down menus. Once again, in that sub it works fine for the first row and after that excel freezes. Tried to limit the range to just few rows, but the result is the same.

 

[Marcelo Branco]

I assumed the data are in a sheet whose name is Sheet1 - adjust the sheet name to suit with your real case.

M.

 

[Marcelo Branco]

The reason i have put everything in Worksheet_Change sub is that everything is triggered by values from 2 drop down menus. Once again, in that sub it works fine for the first row and after that excel freezes. Tried to limit the range to just few rows, but the result is the same.

You hadn't mentioned these drop downs. Tell us exactly what you are trying to do - location of drop downs.

Be careful when using the Worksheet_Change event because an endless loop may be created. When the code changes the value of some cells, on each change, the Worksheet_Change event is triggered (executed) again and again in an endless process ...until Excel freezes...

If you really need to use Worksheet_Change to avoid an endless loop you must use something like

Application.EnableEvents = False
'your code here
Application.EnableEvents = True

M.

 

[wasntme]

Hello Marcelo,

This was an oversight on my part. Fixing it almost got the job done. A bit more explanation on the issue and what i am trying to achieve.
*dates in columns N(offset 13) and P(offset 15) are populated by VBA based on value from a drop down menu. In this case your code works perfect.
*when i change those dates manually the result in Q(offset 16) is not recalculated. Have to select the status in drop down menu to trigger. This is what i am trying to avoid (in general to always show the difference between them if there is data in Range("A3:A100"))
*Maybe need to force recalculation, although i have set them to automatic at start? Sadly i wasn't able to find the right place for it.

Thanks in advance

 

[wasntme]

Didn't see your edit. I have mentioned the drop down lists in my original post, but here is the logic behind them. First one represent different stages of the work process - documents received, applied, finalised and populate the relevant columns. I want to get the difference in days between first and last stage dates.

As a side note i have another drop down list that generates emails to different recipients, subject, body etc. Those work fine.

 

[Marcelo Branco]

Question

Do you want to trigger the Worksheet_Change event when a date either in N3:N100 or in P3:P100 changes?

M.

 

[wasntme]

No, I just need those columns to populate on change event. The one where i need the result i need help with, Q(offset 16), is purely informative, but needs to recalculate if i manually change values in N and P, with condition A not to be empty (actual project present, to avoid empty rows).

Many thanks.

 

[Marcelo Branco]

I'm confused.
Why did you answer "No"?
You are saying "..if i manually change values in N and P, ...".
And in the first post your code checks if A3:A100 is empty
So i understand the code should run when any date in N3:N100 or P3:P100 changes.
Am i right?

M.