Including Formulas in a UserForm

[Pod25]

Hi all.
Another problem. In my worksheet I have a total of 5 sheets. 1 of my sheets has a formula for calculating number of days off taken from a start date and end date, as follows.

Start Date

Please Login or Register  to view this content.

End Date

Please Login or Register  to view this content.

Number of days off

Please Login or Register  to view this content.

So, when using my UserForm to input these days, it is not including the formula, so simply enters the data literally as a date.

Here is my VB code for the UserForm.

Please Login or Register  to view this content.

How can I include the formulas using my UserForm ?
And Im still learning Excel and VB

[gmr4evr1]

Try this..it places the formula in all the cells in the column. I added and If to it so that if column B is blank, column E will be blank...
Employee Attendance2.xlsm

[bakerman2]

Works fine for me now.

[Pod25]

Try this..it places the formula in all the cells in the column. I added and If to it so that if column B is blank, column E will be blank...
Attachment 500570

Thanks for taking the time to look at this

[Pod25]

Works fine for me now.

I like this way, thanks.

[Pod25]

Works fine for me now.

Just one tiny problem.
It is Americanising the date.
I want this date, 6 February 2017, in this format 6/2/2017. But it is reversing the day and month to, 2/6/2017

I have changed the default settings (ctrl +1) but still no change ?

[bakerman2]

Try this.

Please Login or Register  to view this content.

Thanks for adding rep points.

[Pod25]

Ive finally had time to return to this.
Your code certainly fixed my date problem, but has now broken copying the formula?
Ive searched google endlessly, but to no avail.

Try this.

Please Login or Register  to view this content.

Thanks for adding rep points.

[bakerman2]

Have you tried converting the table back to a range and then reconvert it to a table ?

[Pod25]

I will try that now.

[Pod25]

OK, so that did not work.

[bakerman2]

Post the file that doesn't work. Have retried the file I posted in Post#3 and it works every time I save data.

[Pod25]

I just tried your version again and yes, it works.
I will upload my latest version now.

Post the file that doesn't work. Have retried the file I posted in Post#3 and it works every time I save data.

[bakerman2]

How does this work for you ?

[Pod25]

What did you change?
It works pefectly

How does this work for you ?

[bakerman2]

Tables were a nice addition to XL but they can get grumpy sometimes for one reason or anther.
I copied the formula from any row in the Column and pasted it in the topcell of the column, then double-clicked the little black square on the bottom right of the cell so the formula gets re-applied to the columns DataBodyRange.
Et voilà, Xl knows again what to do with the formulas on newly added rows.

[Pod25]

Well done. I was pasting it into the last box, but didnt know about clicking the little black box.