I have a worksheet that is used to plan staff holidays, when a "H" or "h" or "P" is entered against a name and date a macro is triggered that opens the relavent employee's record and pastes the holiday into thier record. This has been working perfectly untill now when data from April is not pasting into the individual record. When stepping into the code, when a character is entered to indicate a holiday the relavent record is found but not the correspondant date, therefore there is no data pasted into the indivudual record. The code used is pasted below, I am wondering if there could be a problem with dates in the employee record. I have attached both if anybody would be kind enough to have a look.
'Add data to the workbook
With Wkb.Sheets("2006")
'Find the row to enter the data based on the date of the data
Set Cel = .Range("B:B").Find(what:=CheckDate, LookIn:=xlValues, _
LookAt:=xlWhole)
'If a matching date is found add the data
If Not Cel Is Nothing Then
ColOffset = Target.Column - _
Sh.Range(Cells(2, Target.Column).Address).MergeArea(1, 1).Column
.Range(Cells(Cel.Row, 3 + ColOffset).Address).Value = Target.Text
SfR Fresh: [clamav-0.94.2.tar.gz] Member README (clamav-0.94.2/README)::
Jan 26, 2004 Possible descriptor leaks in archive unpackers and mishandling of fast .. manager in clamd and support for decoding MS Office VBA macros.
http://www.sfr-fresh.com/unix/misc/clamav-0.94.2.tar.gz:a/clamav-0.94.2/READMEHOME |
End If
End With
Notes:
The Number in column A is the filename of the employee record.
The main workbook containing the holiday calander is named Absence Record and the individual record is 259.
The code in "this workbook" that points to the employee workbook location has to be updated to where ever you save the employee workbook.
Orange cells simply indicate bank or public holidays for planning purposes.
This post was origionally posted on Ozgrid a couple of weeks ago but i have had no luck.
Any help would be much appreciated as this has got me totally stumped.
JP
The four dates in sheet "Apr" are dates in 2005 instead of 2006 and thus will never be found in 259.xls, because there you entered the correct dates. My guess is that you want those dates to be in 2006, right? :)
Correct those dates and your macro should work just fine. Works for me.
Regards,
Wimchester
Winchester,
Thank you so much.
Can't believe that i could have missed that!!! I have spent so much time looking through the code and various forums to see if perhaps there was some sort of problem with excel date handling.
Thank you again.
JP
You're welcome.
Take care,
Wimchester
 Traditional University or MLM University? You Choose
 15 Questions to Ask Your Software Vendor
|
VBA Code Mishandling Dates