Hi all,

I'm working on a leave management solution for my office and it
has 3 worksheets. The first one has the leave application data; the second
has the daywise breakup of available and approved leave slots while the
third sheet has the accrued leave for each employee.

The solution allows the employees to enter the start date and end date for
their leaves and then approves the leave based on two conditions - there
should be enough slots available on that day and there should be a positive
accrued leave balance for the employee.

The problem I'm facing here is that when an employee applies for leave on a
day when there aren't enough slots available, the formula for approval
returns a No. However, it becomes a No for all previous instances where
employees applied for a leave on that day.

What I want is that once a leave application is processed, the approval
formula for that application should stop recalculating. Is there any way this
can be done?

Thanks,

Sam

You can try this

Sub CopyNPasteByValue()

Dim rg As Range
Set rg = Range("A8")
rg = "=RAND()*100"

Debug.Print "Value of rg before finalize " & rg
Application.Calculate ' Simulate calculation
Debug.Print "Value of rg ready for finalize " & rg
rg.Copy ' Line 1
rg.PasteSpecial xlPasteValues ' Line 2
Application.Calculate ' Simulate calculation
Debug.Print "Value of rg after finalize " & rg

End Sub

If this is what you were looking for, then you can modify this sub to accept
range address as the parameter and just call it with the range's address.

The portion of code to do what you asking for really just the Copy and Past
(line 1 and 2), the other are there to demo this code.

Hong Quach


"The Narcissist" wrote:

Hi all,

I'm working on a leave management solution for my office and it
has 3 worksheets. The first one has the leave application data; the second
has the daywise breakup of available and approved leave slots while the
third sheet has the accrued leave for each employee.

The solution allows the employees to enter the start date and end date for
their leaves and then approves the leave based on two conditions - there
should be enough slots available on that day and there should be a positive
accrued leave balance for the employee.

The problem I'm facing here is that when an employee applies for leave on a
day when there aren't enough slots available, the formula for approval
returns a No. However, it becomes a No for all previous instances where
employees applied for a leave on that day.

What I want is that once a leave application is processed, the approval
formula for that application should stop recalculating. Is there any way this
can be done?

Thanks,

Sam