Intersection, Date, and Merged Cell Problem 1

[monagan]

I have a worksheet set up with dates(merged cells) and departments(two separate cells) on the top and names on the side like this:

06/01-03 06/04-10 06/11-17
Ship Main Ship Main Ship Main
Bob
Jim
Lisa
Bill

Now I have another sheet that someone puts in the date and the name and the department, and hours worked in that department.

My question is, how do I (1) Get the date in the right column if it falls in or on the start or end date, then

(2)how do I get it to be in the shipping or maintenance column.

 

[monagan]

I should also add that I have code that will do what I need except It can only be one day, not a start and end date, and can figure out how to work the department yet. This is what I have so far.

Sub GetMISCCOMP()
Dim OpenSheet As String, flName As String, ENAME As String, flPath As String
Dim wbTS As Workbook
Dim lRow As Long, iCol As Long
Dim EHOURS As Variant
Dim EDATE As String


ENAME = Sheets("sheet1").Range("a14", "a14").Value
EDATE = Sheets("sheet1").Cells(7, 8).Value
EHOURS = Sheets("sheet1").Range("a20", "a20").Value

flName = "MISC. HOUR COMP.xls"
flPath = "C:\Documents and Settings\Jon Monagan\My Documents\Gary\"

OpenSheet = Sheets("sheet1").Cells(9, 8).Value
If Not WorkbookIsOpen(flName) Then Workbooks.Open (flPath & flName)
Set wbTS = Workbooks(flName)
wbTS.Activate
wbTS.Sheets(OpenSheet).Activate
lRow = wbTS.Sheets(OpenSheet).Columns(2).Cells.Find(ENAME, LookIn:=xlValues).Row
iCol = wbTS.Sheets(OpenSheet).Rows(2).Cells.Find(EDATE, LookIn:=xlValues).Column
wbTS.Sheets(OpenSheet).Cells(lRow, iCol) = EHOURS

 

[VBAjedi]

Maybe it's just me, but this ain't "clicking" for me. I think you're going to have to make another attempt at explaining the big picture of what you are after, and also give us a better idea of what the finished product looks like.

VBAjedi

 

[monagan]

Ok, sorry

The big Picture:
There is an XL file that when opened it has entries. Kind of like a "form" to be filled out.(Name,Department(maintenance/Shipping),HoursWorked,DateWorked) Then when the submit button is clicked, it opens up another XL file, finds the worksheet for that month, and in this case finds the name,the week that the day worked is in, then finds the department, which is underneath the week, and adds the hours worked to the hours already in that spot.

for instance
Name: Bob
Dept. Maintenance
HoursWorked:5
Date: 7/29

7/12-18 7/20-26 7/28-31
Main Ship Main Ship Main Ship
Jim
Bob 5 0
Bill
Lynn


Thanks VBAJedi

 

[VBAjedi]

Ok... I'd like to suggest a change of layout for your second workbook. If you set it up in columns like this:

Month | Date | Name | Dept | Hours

you will be able to put all entries for all people and all months into a single sheet. Each time your form is filled out, the details are written to the first empty row in this new sheet. This greatly simplifies the code you need, eliminates the need to switch sheets to see other months data, and greatly simplifies reporting. If you apply the AutoFilter to this master data range, you will be able to view only the entries for a given month, a given employee, or any other combination. SUBTOTAL formulas above your data can show the total hours for all visible rows.

If you still need a report in the layout you described (with names down the rows and dates across the top), you can easily construct a pivot table that gives you this view (updated automatically to reflect only the rows the AutoFilter has left visible).

All your data on a single sheet is the way to go...


VBAjedi

 

[SkipVought]

Morgan,

Are you calling these DATES??? SUrely not!
[tt]
06/01-03 06/04-10 06/11-17
[/tt]
I agree with VBA that ALL your data ought to be in ONE table, from which you ought to be able to REPORT for a Month using a Pivot Table. You do not really need a separate field for Month as Date (REAL DATE), THen I'd add a column for Week of the Year, using Weeknum function.

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[monagan]

ok , thanks

I will give your suggestions a try.

Often enough your see in this forum that people are trying to adapt to someone else's doing.
This instance is the same, in which I didn't set up the spreadsheets I'm working with.
Thanks for your input, and I'll get back to you if I have any problems or questions

 

[VBAjedi]

Skip/monagan,

The Month column I suggested is redundant, but my thinking behind it was that, since your existing setup has a seperate sheet for each month, everyone that uses this tool is used to seeing data in 1-month chunks. By including a month column you can, with two clicks, select a given month from the AutoFilter's dropdown list and be seeing what you're used to. If all you have is a date column, you have to select "Custom" in the dropdown list and then create a date range to filter on (about 6 clicks and two typed dates).

It's just a tradeoff: a little data redundancy for more reporting convenience. Your choice!



VBAjedi

 

[SkipVought]

This can still be accomplshed with ONE date field in a PivotTable.

Right click the Date field in the PT.

Select Group & Outline/Group - and select Year, Month & Day

Drag the Year and Month buttons into the Page Field.

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[monagan]

Skip and VBA Jedi,

I am going to try and explain this the best I can. By the way, I appreciate all the advice you've given me.

I have decided to keep the month tabs.

My idea, although not the best, is to have each day have two columns. The same as before, but what I am thinking is this...

days across the top 1-31
so that's going to be 61 columns

So lets say the day is april first.

That would be column C and D

and underneath C is Shipping and D is Maintenance.

I can find the date, then how would I code this when it is Maintenance, find the intersection with the name and column D, and vice versa for Shipping?

 

[SkipVought]

monagan,

You are THINKING in terms of your REPORT FORMAT rather than a DATA FORMAT.

If your DATA were in the format that VBA suggested, the REPORT would be a snap using a PivotTable.

But if you insist on organizing your data in a REPORT format (which will be fraught with problems and will limit your use of the data immensly), I'd suggest duplicating the day value, is 2 columns with 1, 2 columns with 2, etc. using a formula referencing 2 cells to the left plus one.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[monagan]

Ok, you guys got me. Hahaha

I need to change the code a bit, I've been looking and looking, but surprisingly can't find it. I need to open another workbook and open a worksheet named data

 

[monagan]

scratch that, I'm not sure what I was thinking.
I need to know how to add it to the last empty row, like moving down as in VBA's suggestion

 

[monagan]

I found this but am having trouble incorporating it into my needs.
I'm sure it finds the last row, Now how to I put my data in these columns?
Sub NxtRw()
Dim lRow As Long
' Find the FIRST EMPTY row by adding 1 to the last row
lRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row + 1

'Paste the data into the first
'COMPLETELY empty row
ActiveSheet.Paste Destination:=Cells(lRow, 1)

 

[SkipVought]

What is the source of your data?

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[monagan]

There's another workbook and worksheet that this is coming from.

This is what I've got:

lRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row + 1
iCol = 0
Count = 0
Do While Count <> 5
iCol = iCol + 1
Select Case Count
Case "0"
wbTS.Sheets(OpenSheet).Cells(lRow, iCol) = EMonth
Case "1"
wbTS.Sheets(OpenSheet).Cells(lRow, iCol) = EDate
Case "2"
wbTS.Sheets(OpenSheet).Cells(lRow, iCol) = ENAME
Case "3"
wbTS.Sheets(OpenSheet).Cells(lRow, iCol) = EDept
Case "4"
wbTS.Sheets(OpenSheet).Cells(lRow, iCol) = EHOURS
End Select
Count = Count + 1
Loop

The EVariables are coming from the other sheet that the user inputs

 

[SkipVought]

So you have descrete values that a user is supplying.

Sub NxtRw()
Dim lRow As Long
    ' Find the FIRST EMPTY row by adding 1 to the last row
    lRow = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row + 1
    
    'assign the data into the first
    'COMPLETELY empty row
    for c = 1 to 5
      select case c
     Case 1
        Activesheet.Cells(lRow, c) = EMonth
     Case 2
        Activesheet.Cells(lRow, c) = EDate
     Case 3
         Activesheet.Cells(lRow, c) = ENAME
     Case 4
         Activesheet.Cells(lRow, c) = EDept
     Case 5
        Activesheet.Cells(lRow, c) = EHOURS
      end select
    next

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[monagan]

I'm having a problem filling my EVariables.
I'm going to include the code I have because I know I've got to have something really small going wrong here.

This is where I call the sub that you had given me yesterday
Sheet1 is where the user is entering the data
The e variables have a value here, but not inside
NxtRow

Public Sub GetMISCCOMP()
Dim OpenSheet As String, flName As String, ENAME As String, flPath As String
Dim wbTS As Workbook
Dim lRow As Long, iCol As Long
Dim EHOURS As Variant
Dim EDate As String
Dim EMonth As String
Dim EDept As String

ENAME = Sheets("sheet1").Range("a14", "a14").Value
EDate = Sheets("sheet1").Cells(7, 8).Value
EHOURS = Sheets("sheet1").Range("a20", "a20").Value
EDept = Sheets("sheet1").Range("a17", "a17").Value
EMonth = Sheets("sheet1").Range("h9", "h9").Value

flName = "MISC. HOUR COMP.xls"
flPath = "C:\Documents and Settings\Jon Monagan\My Documents\Gary\"
OpenSheet = "Data"
If Not WorkbookIsOpen(flName) Then Workbooks.Open (flPath & flName)
Set wbTS = Workbooks(flName)
wbTS.Activate
wbTS.Sheets(OpenSheet).Activate
Call NxtRw

 

[SkipVought]

With Sheets("sheet1")
   ENAME = .Range("a14").Value
   EDate = .Cells(7, 8).Value
   EHOURS = .Range("a20").Value
   EDept = .Range("a17").Value
   EMonth = .Range("h9").Value
End With

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[monagan]

Would sheet1 have to be in the same workbook as the data worksheet?