Variable for Date not working

p45cal · #19 04-05-2018, 08:36 AM

I see, it doesn't update the values. Even asking the sheet to recalculate doesn't update the values.
Ctrl+Alt+F9 on the keyboard seems to update the sheet (whole workbook I think).
Run this to update the sheet:

Code:

Sub ViewPrjByMth()
With Sheets("PrjByMth").Range("B2:BI15")
  .FormulaR1C1 = "=(IF(ISBLANK(RC1),0,(IF(ISBLANK(R1C),0,prjbymonth(RC1,R1C)))))"
  .Value = .Value 'if you disable this line by adding an apostrophe before it, the sheet will have all your formulae back in.
End With
End Sub

The problem lies with the function's arguments on the sheet not referring directly to the data on the FYnnnn sheets; Excel knows to recalculate a formula when any cell in its arguments changes, which is not the case here.
You could, instead of running this code, leave your formulae as they are in the sheets but you'd need to add Application Volatile directly after the Dim statement in the PrjByMonth function's code. This brings in its own set of problems (lots of time recalculating the sheeet unnecessarily which you could control by setting Calculation to manual).

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
I want to create a bar chart of multiple variable. Then I need to draw trend lin of those variable	shimulsiddiquee	Excel	1	05-16-2017 07:39 AM
Run Time Error '91': Object variable or With block variable not set using Catalogue Mailmerge	Berryblue	Mail Merge	1	11-13-2014 05:36 PM
Run-time error 91 object variable or with block variable not set	JUST ME	Word VBA	4	03-25-2014 06:56 AM
Run-time error '91': Object variable or With block variable not set	tinfanide	Excel Programming	2	06-10-2012 10:17 AM
30+ days Variable Day Date Calculations via Fields	ztag	Word	2	01-06-2012 11:12 AM