Windows Live Messenger: Excel: How to make a list based on the date entered

Excel: How to make a list based on the date entered
I am using Excel from a long time but I am not able to solve the problem I am facing. The problem is, I have Excel sheet with some set of names and those peoples whose name is on sheet are registering their movements. I am trying to create a sheet “Dairy” to plan their holiday time. To maintain a certain skill set all the times there are some set of rules are defined regarding holidays, and these rules are all essentials for all 208 peoples in the business. So I need a way to check quickly if for a time being manager is away on a particular date. This is clearly shown in my example workbook on the “Dairy” tab.

I am very grateful in advance for any suggested solution.
Reply With Quote
#2
Old 31-03-2011
ADJATAY ADJATAY is offline
Member

Join Date: May 2009
Posts: 196
Re: Excel: How to make a list based on the date entered
I think you have good knowledge but still you’re lacking in vocabulary. I am providing you the code, to use that code you to go through some steps first you’ve to open the VBE. After that you have to go to the Dairy sheet. And then paste the following code:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

If (Target.Address <> "$C$2") Then Exit Sub
If Target.Value = vbNullString Then Exit Sub

If IsDate(Target) Then
Application.EnableEvents = False
Call doLocateMovement(Target.Value)
Application.EnableEvents = True

Else
MsgBox "Please enter a valid date.", vbCritical, "Invalid Date"
Application.EnableEvents = False
Target = vbNullString
Application.EnableEvents = True
End If
End Sub

Private Sub doLocateMovement(sDate As String)

Dim lTgtRow As Long
Dim rngTarget As Range
Dim sMove As String
Dim sDiary As String
Dim iCol As Integer
Dim iMaxCol As Integer
Dim iJobTitleRow As Integer
Dim iNameRow As Integer
Dim iRotationRow As Integer
Dim dicJobTitle As Object
Dim sJobTitle As String
Dim sName As String
Dim sRotation As String
Dim sReasonCode As String
Dim vOption As Variant
Dim arrRecGroup As Variant
Dim arrRecDetail As Variant
Dim lRecGroup As Long
Dim lRecDetail As Long
Dim lDetailRow As Long

iJobTitleRow = 4
iNameRow = 5
iRotationRow = 6

sMoveSheet = "Movements"
sDiary = "Diary"
With Sheets(sMoveSheet)

' locate the date text on column A
lTgtRow = getItemRowLocation(sDate, .Name, Range("A:A"))
If (lTgtRow < 1) _ Then MsgBox sDate & " not found in sheet " & .Name Exit Sub End If ' locate last column used on the row located iMaxCol = getLastColumn(.Name, Range(Cells(1, "B"), Cells(Rows.Count, Columns.Count))) If (iMaxCol = 0) _ Then MsgBox "No record found for date " & sDate, vbInformation, "Record Not Found" Exit Sub End If Set dicJobTitle = CreateObject("Scripting.Dictionary") For iCol = 2 To iMaxCol If (.Cells(lTgtRow, iCol) <> vbNullString) _
Then
sJobTitle = .Cells(iJobTitleRow, iCol)
sName = .Cells(iNameRow, iCol)
sRotation = .Cells(iRotationRow, iCol)
sReasonCode = .Cells(lTgtRow, iCol)
If (dicJobTitle.Exists(sJobTitle)) _
Then
dicJobTitle(sJobTitle) = dicJobTitle(sJobTitle) & "~" & sName & "|" & sRotation & "|" & sReasonCode
Else
dicJobTitle.Add Key:=sJobTitle, Item:=sName & "|" & sRotation & "|" & sReasonCode
End If
End If
Next
End With

With Sheets(sDiary)
lDetailRow = 8
.Range(.Cells(lDetailRow, 1), .Cells(.Rows.Count, .Columns.Count)).Clear

For Each vOption In dicJobTitle
sJobTitle = CStr(vOption)
.Cells(lDetailRow, "C") = sJobTitle

lDetailRow = lDetailRow + 1
arrRecGroup = Split(CStr(dicJobTitle(sJobTitle)), "~")
For lRecGroup = LBound(arrRecGroup) To UBound(arrRecGroup)
arrRecDetail = Split(arrRecGroup(lRecGroup), "|")

.Cells(lDetailRow, "C") = arrRecDetail(0)
.Cells(lDetailRow, "D") = arrRecDetail(1)
.Cells(lDetailRow, "E") = arrRecDetail(2)
lDetailRow = lDetailRow + 1
Next lRecGroup
lDetailRow = lDetailRow + 1
Next
End With

End Sub

Function getItemRowLocation(sLookFor As String, _
sSheetName As String, _
Optional myRange As Range = Nothing, _
Optional bFullString As Boolean = True, _
Optional bLastOccurance As Boolean = True) As Long
' get last use row on the sheet

Dim Cell As Range
Dim iLookAt As Integer
Dim iSearchDir As Integer
' Dim rngFirst As Range
Dim rngSearch As Range

If (bFullString) _
Then
iLookAt = xlWhole
Else
iLookAt = xlPart
End If

If (bLastOccurance) _
Then
iSearchDir = xlPrevious
Else
iSearchDir = xlNext
End If

If myRange Is Nothing _
Then
Set rngSearch = Sheets(sSheetName).Cells
Else
Set rngSearch = Sheets(sSheetName).Range(myRange.Address)
End If

With rngSearch
If (bLastOccurance) _
Then
Set Cell = .Find(sLookFor, .Cells(1, 1), , iLookAt, xlByRows, iSearchDir)
Else
Set Cell = .Find(sLookFor, .Cells(.Rows.Count, .Columns.Count), , iLookAt, xlByRows, iSearchDir)
End If
End With

If Cell Is Nothing Then
getItemRowLocation = 0
Else
getItemRowLocation = Cell.Row
End If
Set Cell = Nothing
Set rngSearch = Nothing
End Function

Function getLastColumn(sSheetName As String, Optional myRange As Range = Nothing) As Long
' get last use row on the sheet

Dim Cell As Range

If (myRange Is Nothing) _
Then
Set Cell = Sheets(sSheetName).Cells.Find("*", Cells(1, 1), , , xlByColumns, xlPrevious)
Else
Set Cell = Sheets(sSheetName).Range(myRange.Address).Find("*", Sheets(sSheetName).Range(myRange.Address).Cells(1, 1), , , xlByColumns, xlPrevious)
End If
If Cell Is Nothing Then
getLastColumn = 0
Else
getLastColumn = Cell.Column
End If
Set Cell = Nothing

End Function

Reply With Quote
#3
Old 31-03-2011
Sadiee Sadiee is offline
Member

Join Date: Jun 2009
Posts: 660
Re: Excel: How to make a list based on the date entered
I would like to discuss some basic things on this matter:

1. Find the date on the movement sheet.
2. If row is found then start moving on the same row column by column to check if there is any text in any column.
3. While checking if the text is found then we have to check whether we have came across that job title before while scanning through particular row. If job title is appeared before then name and rotation are added to the last entry of the job title but if job title have not appeared before then name and rotation for that job title are recorded.
4. After checking all the columns of the row we have to start outputting for the each name, job titles and rotaion.

Reply With Quote
#4
Old 31-03-2011
Lankesh Lankesh is offline
Member

Join Date: Nov 2009
Posts: 592
Re: Excel: How to make a list based on the date entered
Can you tell me how exactly the date is being shown in the error message? And check if the date has displayed in same format in the movement sheet. I had attached the work book and I think it had result in it already, in that case click on the search date cell in the dairy sheet then got to the formula bar and just press enter. Now tell me have you got result or again same error message is appeared?
Reply With Quote
#5
Old 31-03-2011
Appaji Appaji is offline
Member

Join Date: Feb 2010
Posts: 528
Re: Excel: How to make a list based on the date entered
First of all thanks for the reply, I have tried all kind of formats to represent date dd/mm/yyyy, m/d/yyyy etc. even I have copy-pasted the date formats from the movement tab and dairy to make sure that they are in same format but still no positive result. The file you have uploaded with the name of the peoples gives me one hope, one suggestion is that date format may be won’t work because I’m in UK and I guess you are not? So may be my machine will be formatting the dates with some other format or differently. It came to my mind when I have opened your WB date format shows as in the yellow box “14/01/2011” but in your post you have suggested formatting “m/d/yyyy”.
Reply With Quote
#6
Old 31-03-2011
Shawn-Roger Shawn-Roger is offline
Member

Join Date: Apr 2010
Posts: 76
Re: Excel: How to make a list based on the date entered
Ok, I just want to see how dates looks like in both the examples, so could you post screen shots of both the sheets? May be then I will be able to figure out. Since, you have already told me that you are able to change the format of the dates in the movement sheet and also you have tried to a search using same format in dairy as well, I am very confused right now, May be by looking at the screen shots I will be able to understand what’s going on or how to solve this problem.
Reply With Quote