+ Reply to Thread
Results 1 to 9 of 9

Excel 2007 : Formula that translate dates to a calendar

  1. #1
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2010
    Posts
    157

    Formula that translate dates to a calendar

    I have a spreedsheet that provides me with data that includes employee names, employee numbers, dates he/she worked and a coded shift.

    I want to take the dates that are provided and have the shift codes appear under the appropriate date in a horizontal calendar. I've been trying to use a vLookup formula, but it's not working the way I had hoped.

    I've attached a sample sheet with the infomation as I get it and two calendars. One calendar has my failed vLookup formula and the other is the way that I want the data to appear.
    It's importat to note that there are 2 shifts on the form (and possibly more in the future) and each employee can work up to 2 shifts in any one day. The shifts are AS1 and AR1. The AS1 shift is more important and I would like to see it represented on the calendar first.

    Any help would be appreciated.

    Also, if a macro would be a better option, I'll need help with that too.
    Attached Files Attached Files
    Last edited by Ricker090; 04-11-2011 at 06:20 PM.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Formula that translate dates to a calendar

    Try this macro

    Please Login or Register  to view this content.
    Martin

  3. #3
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: Formula that translate dates to a calendar

    I'm getting a Run-Time error stating "Object variable or With block variable not set."

    I have attached a snapshot of the VB editor.
    Attached Images Attached Images

  4. #4
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Formula that translate dates to a calendar

    I suspect that you have some employees on sheet1 which are not on the calendar. If you hover over the N variable in the code, you will see which line is the problem,

    Make sure that you delete the instruction box on the sample.

  5. #5
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: Formula that translate dates to a calendar

    It works now that I deleted the instruction box in the sample. Thanks or the help/

  6. #6
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: Formula that translate dates to a calendar

    I was wondering if you could help me translate this code some, so I can make adjustments.

    What is the code in red doing?

    Sub Test()
    Sheets("Employee Shifts").Activate
    For N = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    TargetRow = Sheets("Calendar").Columns(1).Find(Cells(N, 1), , xlValues, xlWhole).Row
    For M = 3 To Cells(1, Columns.Count).End(xlToLeft).Column Step 2
    If Cells(N, M) <> "" Then
    TargetColumn = Sheets("Calendar").Columns(1).Find("Emp#", , xlValues, xlWhole).EntireRow.Find(CDate(Format(Cells(N, M), "Short Date")), , xlFormulas, xlWhole).Column
    If Sheets("Calendar").Cells(TargetRow, TargetColumn) <> "AS1" Then
    Sheets("Calendar").Cells(TargetRow, TargetColumn) = Cells(N, M + 1)
    End If
    End If
    Next M
    Next N
    End Sub
    Last edited by Ricker090; 04-12-2011 at 02:50 PM.

  7. #7
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Formula that translate dates to a calendar

    The lines in read are the start of loops, one inside the other.

    The constructs containing the End method are used to work out where the last row and column are respectively. This allows to code to automatically adjust for changes in the width and depth of the data set.

    Hope this helps.

  8. #8
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: Formula that translate dates to a calendar

    Yes it does help some. Thanks.

    I'm still having trouble modifying the code. I have added three columns of information to the left of the Emp# on the both sheets, and it's having issues. The three columns are Location, Seniority, and Qualifications. I use the vlookup formula that retrieves the information I need for those columns though.

    Do you have any suggestions?
    Last edited by Ricker090; 04-13-2011 at 10:41 AM.

  9. #9
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Formula that translate dates to a calendar

    If you have introduced extra columns...

    Please Login or Register  to view this content.
    .... will not work anymore as the code is looking the emplyee number in column 1 (A).

    Change the 1 to whatever number column the Emp# is now in - I'm guessing at 4 from your description.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1