Code modification for updating

[sujittalukde]

Also posted at mrexcel forum but not getting suitable reply. Hope, someone here will me to sort out the problem
http://www.mrexcel.com/board2/viewtopic.php?t=280369

I have a code which extracts date from one sheet (named Attendance) and put them to another sheet (named Leave) in a cell The code works fine but when the cell in Attendance sheet changed, corresponding update doesnot happen in Leave sheet. For eg,

If you put cell F19 as CL in Attendance sheet it will put the date say 01/06/07 ,under which CL is written The date 01/06/07 will be copied to the target cell in Leave sheet. Now if you delete the CL in the cell in Attendance sheet/ or change CL to PL etc., the code doesnot delete / or change the date in the Leave sheet.

I want that the date should also be deleted / updated in the Leave sheet

A sample WB is attached to clarify the position.
In case any clarification is needed, please ask for the same.

[Simon Lloyd]

You could try a formula instead, something like:


=IF(Attendance!C19="",Leave!K15="",Attendance!C17)
this way if C19 which has "CL" was blank then there will be no date displayed.

[sujittalukde]

Thanks for the reply but this formula I cant use as this will extract only one date not more than one date. See attached.

[mdmackillop]

This should handle CL. Others can be implemented in similar fashion. You'll need to add a little code to lose ",," where a date is deleted. Note that the code depends on a selection change between value changes.
Option Explicit
'Variable to store previous value
Dim LastVal As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("C19:AG65000")) Is Nothing Then LastVal = Target
End Sub

'your code; add call statement
End If
Call ClearLast(dayte, r)
End If
endo:
End Sub



Sub ClearLast(dayte As Date, r As Long)
Dim Rng As Range
Select Case LastVal
Case "CL"
Set Rng = Sheets("Leave").Cells(r - 4, "K")
If InStr(1, Rng, dayte) > 0 Then
Rng = Application.WorksheetFunction.Substitute(Rng, dayte, "")
End If
End Select
End Sub

[sujittalukde]

Many many thanks for the reply. Where to put your first code?In attendance sheet code or somewhere else? Also where shall I put my original code and how can I call my worksheet code? please guide me.

[mdmackillop]

As attached

[mdmackillop]

Also posted at mrexcel forum but not getting suitable reply. Hope, someone here will me to sort out the problem
http://www.mrexcel.com/board2/viewtopic.php?t=280369


As pointed out on the other site, you should advise of the cross post in both locations.

[sujittalukde]

OK in future I'll keep the same in mind what I thought as the matter was pending there so did not post there for posting the same here.Anyway, I'll do the same in future.
In the mean time I was testing your attached file but the problem of not updating the date on changing CL to any other say PL or P etc., the dates are not deleting on Leave sheet.Where the problem is going on?

[mdmackillop]

Our purpose is not to provide free solutions but to help educate. I've shown how it is done for one value; you need to try to adjust the code for the other changes. If you have problems, let us know, but we need your effort as well.