PDA

View Full Version : Solved: Fill blank cells with date using a criteria



jenroyce
01-10-2011, 11:08 PM
Hi,

I'm working on a Vacation leave tracker that will automatically enter dates in a separate sheet when the word "VL" is entered on a certain date/cell from a different sheet.

Aussiebear
01-11-2011, 01:32 AM
Hi jenroyce, Welcome to VBAX. Please post a sample workbook so we can see your layout. To post a workbook, click on "Go Advanced" scroll down to Manage Attachments and follow the prompts from there.

jenroyce
01-11-2011, 04:45 AM
Hi! Thank you! I'm glad to have found vbaexpress, looking forward to learning more :)

Please see attached.

Tab "Leave Calendar" is where the dates should populate when the words "VL" "SL" "PL" is entered to the month tab ("Jan" tab)

The "Jan" tab is where i will keep track if an employee was late, no sick or vaca leave. So i will enter the word "VL" or "SL" i want the date where i entered the work "VL" or "SL" or "PL" populate into the "Leave Calendar" tab.

I hope you can help me. if i have the right code i can expand it to a whole year's worth of info. i just need instructions how to do it :)

shrivallabha
01-11-2011, 06:20 AM
I have attached a basic code which will update entries for VL, SL, PL. I have hard-coded some part based on the sample provided (Range addresses in the Leave Calendar). Flexible coding may need some additional efforts. But I hope this will put you on track.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyVal As String
Dim RVal As Long
Dim R As Range
Dim Eid As String
MyVal = ActiveCell.Offset(-1, 0).Value 'Determining the type of Leave
RVal = ActiveCell.Offset(-1, 0).Row 'For finding the Employee ID
Eid = Range("B" & RVal).Value 'Finding the corressponding Employee ID
Select Case MyVal
Case "VL"
With ActiveSheet
Set R = Sheets("Leave Calendar").Range("B4:B15").Find(What:=Eid, _
LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
If R Is Nothing Then
MsgBox Eid & "Not Found in the Leave Calendar!"
Else
R.End(xlToRight).Offset(, 1).Value = .Cells(1, ActiveCell.Column).Value
End If
End With
Case "SL"
'Entire Code in Each case is the same except the range reference
With ActiveSheet
Set R = Sheets("Leave Calendar").Range("B21:B32").Find(What:=Eid, _
LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
If R Is Nothing Then
MsgBox Eid & "Not Found in the Leave Calendar!"
Else
R.End(xlToRight).Offset(, 1).Value = .Cells(1, ActiveCell.Column).Value
End If
End With
Case "PL"
With ActiveSheet
Set R = Sheets("Leave Calendar").Range("B38:B49").Find(What:=Eid, _
LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
If R Is Nothing Then
MsgBox Eid & "Not Found in the Leave Calendar!"
Else
R.End(xlToRight).Offset(, 1).Value = .Cells(1, ActiveCell.Column).Value
End If
End With
End Select
End Sub


I have attached updated xlsm.

jenroyce
01-12-2011, 10:03 AM
This is A-M-A-Z-I-N-G! I tried creating another tab for another month and when i change the date an error occurs... what string should i change so i wont get the error? THANK YOU Shrivallabha!!

shrivallabha
01-12-2011, 10:25 AM
You will need to keep this same procedure for all months. You can do this by simply copying the Jan worksheet and then editing to the next month (because I have used Activesheet). Remember this is a basic code and improve it once you understand.