Formulas feeding with VBA populated range

[wasntme]

Hello,

I have sheet (VAT registrations) where certain columns are populated by VBA with todays date (N and P), based on condition selected by a drop down list. I have another summary sheet with few SUMIFS and COUNTIFS formulas, with ranges the columns populated by the VBA (N and P).

The problem i am encountering is that those formula results do not update when the values in the range are populated by VBA, and i need to enter values there manually (or simply activate and ENTER those cells). When i make manual changes they work fine.

Sample formula (others just have different range in the same sheet). Formulas just need to count/sum entries in this month:

=COUNTIFS('VAT registrations'!P:P,">="&EOMONTH(TODAY(),-1)+1,'VAT registrations'!P:P,"<"&EOMONTH(TODAY(),0)+1,'VAT registrations'!E:E,"Registration",'VAT registrations'!F:F,"Finalised")

Is there a way to fix this and get the counters working with the VBA values?

Thanks in advance.

 

[Joe4]

COUNTIFS and SUMIFS are normal, volatile Excel functions that should update immediately.
If they are not updating immediately upon the data changing, it most likely means that you have your workbook set to Manual Calculation mode.
Try setting it to Automatic.
See: https://support.office.com/en-us/ar...recision-73fc7dac-91cf-4d36-86e8-67124f6bcce4

 

[wasntme]

Hi Joe,

Excel calculations are set on Automatic. I have also put

Application.Calculation = xlCalculationAutomatic

at start of my sub that populates the dates (it is worksheet_change) just in case. Sadly this does not sort the issue - still need to update the cells manually (even if i change/reselect all calculation options in Excel). As i understand VBA stops formula calculations when running the code (might be wrong, i am still learning VBA).

I have tried to fix it with

Worksheets("Summary").Calculate

at end of the sub (thats the sheet where countifs/sumifs are), but it also doesnt work.

 

[Snakehips]

It is possible that your population code is purposely setting calculation to manual, in order to prevent slow processing of the data.

That would involve a line of code.

 Application.Calculation = xlCalculationManual

If that has not been reset at the end of the code by

Application.Calculation = xlCalculationAutomatic

then manual calculation will persist.

You could add that, if it suits your purposes.

Hope that helps?

 

[Joe4]

I am curious. Instead of "re-entering" the formula to get it to update, if you press F9, does that update the values?
If so, then your calculations are somehow being switched to manual.

Also, is there anything in your code that turns off Screen Updating?
If so, are you turning it back on at the end of your code?

 

[wasntme]

I am curious. Instead of "re-entering" the formula to get it to update, if you press F9, does that update the values?
If so, then your calculations are somehow being switched to manual.

Also, is there anything in your code that turns off Screen Updating?
If so, are you turning it back on at the end of your code?

I am not re-entering the formula, but the range used in it, that is populated by VBA (column P). VBA use this line of code for populating, if it gives any clue:

Cells(Target.Row, "P") = Format(Now(), "dd/mm/yyyy")

Tried with F9, but nothing happens.

Have turned Screen Updating and Application calculation on automatic at the end of the sub, just in case with hope it will fix it, but also no success

 

[Joe4]

Cells(Target.Row, "P") = Format(Now(), "dd/mm/yyyy")

Depending on regional settings, that may enter the value as a Text instead of a Date, which may not be picked up by formulas that run against that cell.
You can usually tell by the alignment of the entry. Minus any extra formatting, text entries are left-justified, while date and numeric entries are right-justified.

Really, you should just populate the cell with the date, and let cell formatting display it how you desire, i.e.

Cells(Target.Row, "P") = Date
Cells(Target.Row, "P").NumberFormat = "dd/mm/yyyy"

 

[wasntme]

Hi Joe4,

I think thats it! Will need to test it a bit and apply to other ranges, but right now works perfect!

As a note the cell entries were date/numeric (right-justified), but switching my code with yours does the trick.

Thank you so much!

 

[Joe4]

You are welcome.