Code Problem

[zyhin]

Hi All,

I have this code I am trying to get to work. I have a time sheet for all employees and want to automate the process a bit.

On Worksheet "Employee" I have 3 columns(Employee Number(A), Employee Name(B), Area(C). I am trying to fill in the time sheet with each employee and print it out then move to the next one...and so on.

Sub RunPrint()
    
    Dim Employee As Range
    Dim EmployeeName As String
    Dim WeekStart As Date
    Dim WeekEnd As Date
    Dim Area As String
    
    WeekStart = InputBox("Please enter week beginning date. **/**/**")
    WeekEnd = WeekStart + 6
    
    For Each Employee In Worksheets("Employee").Range("A2:A65536")
    
        With Employee
        
            If Employee = True Then
                
                If Range(Employee, 3) = "P/C" Then
                    
                    Worksheets("TimeSheet").Cells("N1") = ("EMPLOYEE NAME: " & EmployeeName & "   " & EmployeeNumber)
                    Worksheets("TimeSheet").Cells("A3") = ("WEEK BEGINNING: " & WeekStart)
                    Worksheets("TimeSheet").Cells("N3") = ("WEEK ENDING: " & WeekEnd & "  " & Area)
                    Worksheets("TimeSheet").PrintOut Copies:=2
                Else
                    Worksheets("TimeSheet").Cells("N1") = ("EMPLOYEE NAME: " & EmployeeName & "   " & EmployeeNumber)
                    Worksheets("TimeSheet").Cells("A3") = ("WEEK BEGINNING: " & WeekStart)
                    Worksheets("TimeSheet").Cells("N3") = ("WEEK ENDING: " & WeekEnd & "  " & Area)
                    Worksheets("TimeSheet").PrintOut Copies:=1
                End If
                
            End If
        
        End With
        
    Next Employee
        
   
End Sub

Any help would be great. Z

 

[bertie]

The first problem is getting the WeekStart from an InputBox.

An InputBox returns a value of data type string. So if you want to add six to get the WeekEnd you need to convert the InputBox value into a date.

I haven't done any validation on the user input other than wrap an error trap around it.

    [color=green]'validate date input[/color]
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
        tempDate = InputBox("Please enter week beginning date. **/**/**")
        WeekStart = Format([COLOR="Red"]CDate[/COLOR](tempDate), "dd/mm/yy")
        WeekEnd = Format(WeekStart + 6, "dd/mm/yy")
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0 [color=green]'report errors as normal[/color]

The code then loops through the Employee's until it reaches an empty cell.

    [color=green]'loop through employees[/color]
    [color=darkblue]Set[/color] rngEmp = Sheets("Employee").Range("A2")
    [color=darkblue]Do[/color] [color=darkblue]Until[/color] rngEmp = ""

It then populate the TimeSheet and prints out according to the value of the AREA. Then move to the next record.


The full code is shown below. I have commented out the Print statements for testing.

[color=darkblue]Sub[/color] RunPrint()
    [color=darkblue]Dim[/color] rngEmp [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] WeekStart [color=darkblue]As[/color] [color=darkblue]Date[/color]
    [color=darkblue]Dim[/color] WeekEnd [color=darkblue]As[/color] [color=darkblue]Date[/color]
    [color=darkblue]Dim[/color] tempDate [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] empName [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] empNum [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] empArea [color=darkblue]As[/color] [color=darkblue]String[/color]
    
    [color=green]'validate date input[/color]
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
        tempDate = InputBox("Please enter week beginning date. **/**/**")
        WeekStart = Format(CDate(tempDate), "dd/mm/yy")
        WeekEnd = Format(WeekStart + 6, "dd/mm/yy")
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0 [color=green]'report errors as normal[/color]
        
    
    [color=green]'loop through employees[/color]
    [color=darkblue]Set[/color] rngEmp = Sheets("Employee").Range("A2")
    [color=darkblue]Do[/color] [color=darkblue]Until[/color] rngEmp = ""
        
        [color=green]'==================[/color]
        [color=green]'Populate TimeSheet[/color]
        [color=green]'==================[/color]
        empNum = rngEmp.Value
        empName = rngEmp.Offset(, 1).Value
        empArea = rngEmp.Offset(, 2).Value
        [color=darkblue]With[/color] Sheets("TimeSheet")
            .Range("N2").Value = "EMPLOYEE NAME: " & empName & "    " & empNum
            .Range("A3").Value = "WEEK BEGINNING: " & WeekStart
            .Range("N3").Value = "WEEK ENDING: " & WeekEnd & "   " & empArea
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        
        [color=green]'============[/color]
        'Print out
        [color=green]'============[/color]
        [color=darkblue]If[/color] UCase(rngEmp.Offset(, 2).Value) = "P/C" [color=darkblue]Then[/color]
            [color=green]'.PrintOut Copies:=2[/color]
        [color=darkblue]Else[/color]
            [color=green]'.PrintOut Copies:=1[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
        
        [color=green]'next employee[/color]
        [color=darkblue]Set[/color] rngEmp = rngEmp.Offset(1, 0)
    [color=darkblue]Loop[/color]
    
End [color=darkblue]Sub[/color]

PS Move the END WITH to after the PRINT OUT