Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Multiple If statement not working in code 3

Status
Not open for further replies.
marshybid

marshybid

Technical User
Nov 30, 2007
8
GB
Hi There,

I am new to this forum and would like to request some suggestions for making my macro work correctly.

I have a set of data that I need to sort and create pivot tables with 2-3 times per week.

I will insert the code that I am using for reference. The issue is that when the data is sorted for 'Approved Timesheets' I need it to be based on 2 criteria;

My Code:
Code: 
If myBaseRow.Cells.Item(1, 21) <> "Approved" Then If myBaseRow.Cells.Item(1, 44) <=StartDate And myBaseRow.Cells.Item(1, 44) >=EndDate Then

The first If statement works fine, but the second appears to be ignored?? I have put this part of the code in blue to make it easily visible.

Can anyone suggest a resolve please.

Full macro
Code: 
Private  Function PTSubtotals(ByRef PTField As PivotField) 
     
    PTField.Subtotals = _ 
    Array(False, False, False, False, False, False, False, False, False, False, False, False) 
End Function 
Sub Timesheets() 
     '
     ' Timesheets Macro
     ' Timesheet Filter 4/22/2008 by Richard Francis
     '
    Dim myWorkBook As Workbook 
    Dim myBaseWorkSheet As Worksheet 
    Dim myBaseRange As Range 
    Dim myBaseRow As Range 
    Dim RowsCounter As Long 
    Dim StartDate As Date 
    Dim EndDate As Date 
    StartDate =  Format(Date, "mm/dd/yyyy") 
    EndDate = Format(Date, "mm/dd/yyyy") 
     
    On  Error Resume Next 
    StartDate = Application.InputBox("Enter start date", Type:=2) 
    On Error Goto 0 
    If StartDate > 0 Then 
         
        On Error Resume Next 
        EndDate = Application.InputBox("Enter end date", Type:=2) 
        On Error Goto 0 
        If EndDate > 0 Then 
             
            If EndDate < StartDate Then 
                 
                 MsgBox "Start date can not be earlier than end date" 
            Else 
                 
            End If 
        End If 
    End If 
    Application. ScreenUpdating = False 
     '// Add required columns and formulas
    Sheets("Timesheet Details").Select 
    Range("B1").Select 
    ActiveCell.FormulaR1C1 = "Order ID" 
    Range("A1:AR1").Select 
    With Selection.Interior 
        .ColorIndex = 6 
        .Pattern = xlSolid 
    End With 
    Selection.Font.Bold = True 
    Selection.AutoFilter 
    Range("A1").Select 
    Columns("Y:Y").Select 
    Selection.Insert Shift:=xlToRight 
    Range("Y1").Select 
    ActiveCell.FormulaR1C1 = "Timesheet For Week Ending" 
    Range("Y2").Select 
    ActiveCell.FormulaR1C1 = "=RC[-1]+CHOOSE(WEEKDAY(RC[-1]),0,6,5,4,3,2,1)" 
    Range("Y2").Select 
    ActiveCell.Offset(0, -1).Range("A1").Select 
    Selection.End(xlDown).Select 
    ActiveCell.Offset(0, 1).Range("A1").Select 
    ActiveCell.FormulaR1C1 = "1" 
    Selection.End(xlUp).Select 
    Selection.Copy 
    Range(Selection, Selection.End(xlDown)).Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
    ActiveCell.Select 
    Columns("AS:AS").Select 
    Selection.Insert Shift:=xlToRight 
    Range("AS1").Select 
    ActiveCell.FormulaR1C1 = "Approved in Week Ending" 
    Range("AS2").Select 
    ActiveCell.FormulaR1C1 = "=RC[-1]+CHOOSE(WEEKDAY(RC[-1]),0,6,5,4,3,2,1)" 
    Range("AS2").Select 
    ActiveCell.Offset(0, -1).Range("A1").Select 
    Selection.End(xlDown).Select 
    ActiveCell.Offset(0, 1).Range("A1").Select 
    ActiveCell.FormulaR1C1 = "1" 
    Selection.End(xlUp).Select 
    Selection.Copy 
    Range(Selection, Selection.End(xlDown)).Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
    ActiveCell.Select 
    Columns("AT:AT").Select 
    Selection.Insert Shift:=xlToRight 
    Range("AT1").Select 
    ActiveCell.FormulaR1C1 = "Total Amount" 
    Range("AT2").Select 
    ActiveCell.FormulaR1C1 = _ 
    "=SUM(RC[-14]*RC[-31])+(RC[-13]*RC[-30])+(RC[-12]*RC[-29])" 
    Columns("AT:AT").Select 
    Selection.NumberFormat = _ 
    "_-[$£-809]* #,##0.00_-;-[$£-809]* #,##0.00_-;_-[$£-809]* ""-""??_-;_-@_-" 
    Range("AT2").Select 
    ActiveCell.Offset(0, -1).Range("A1").Select 
    Selection.End(xlDown).Select 
    ActiveCell.Offset(0, 1).Range("A1").Select 
    ActiveCell.FormulaR1C1 = "1" 
    Selection.End(xlUp).Select 
    Selection.Copy 
    Range(Selection, Selection.End(xlDown)).Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
    ActiveCell.Select 
    Range("A1").Select 
     
     '// Add Additional Sheets
    Sheets.Add 
    Sheets("Sheet1").Select 
    Sheets("Sheet1").Name = "Approved Timesheets" 
    Sheets.Add 
    Sheets("Sheet2").Select 
    Sheets("Sheet2").Name = "Approved Timesheets Pivot" 
    Sheets.Add 
    Sheets("Sheet3").Select 
    Sheets("Sheet3").Name = "Pending Timesheets" 
    Sheets.Add 
    Sheets("Sheet4").Select 
    Sheets("Sheet4").Name = "Pending Timesheets Pivot" 
    Sheets.Add 
    Sheets("Sheet5").Select 
    Sheets("Sheet5").Name = "Declined Timesheets" 
    Sheets.Add 
    Sheets("Sheet6").Select 
    Sheets("Sheet6").Name = "Declined Timesheets Pivot" 
     
     
     '// Copy Data to correct Sheets
    Sheets("Timesheet Details").Select 
    Cells.Select 
    Application.CutCopyMode = False 
    Selection.Copy 
    Sheets("Approved Timesheets").Select 
    ActiveSheet.Paste 
    Sheets("Pending Timesheets").Select 
    ActiveSheet.Paste 
    Sheets("Declined Timesheets").Select 
    ActiveSheet.Paste 
    Sheets("Approved Timesheets").Select 
    Rows("1:1").Select 
    Application.CutCopyMode = False 
    Selection.AutoFilter 
    Sheets("Pending Timesheets").Select 
    Rows("1:1").Select 
    Selection.AutoFilter 
    Sheets("Declined Timesheets").Select 
    Rows("1:1").Select 
    Selection.AutoFilter 
    Sheets("Timesheet Details").Select 
    Range("A1").Select 
     
     '//  Delete non required data from each worksheet
   [COLOR=blue] Sheets("Approved Timesheets").Select 
    Set myBaseWorkSheet =  ActiveWorkbook.ActiveSheet 
    Set myBaseRange = myBaseWorkSheet.Rows 
    For RowsCounter = myBaseRange.Rows.Count To 2  Step -1 
        Set myBaseRow = myBaseRange.Item(RowsCounter) 
        If Len(myBaseRow.Cells.Item(1, 24)) <> 0 Then 
             
            If myBaseRow.Cells.Item(1, 21) <> "Approved" Then 
                 
                 '// Delete data that we do not need for each tab
                If myBaseRow.Cells.Item(1, 44) >= StartDate And myBaseRow.Cells.Item(1, 44) <= EndDate Then 
                    myBaseRow.Delete 
                     
                End If 
            End If 
        End If 
        Next [/color]
        Sheets("Pending Timesheets").Select 
        Set myBaseWorkSheet = ActiveWorkbook.ActiveSheet 
        Set myBaseRange = myBaseWorkSheet.Rows 
        For RowsCounter = myBaseRange.Rows.Count To 2 Step -1 
            Set myBaseRow = myBaseRange.Item(RowsCounter) 
            If Len(myBaseRow.Cells.Item(1, 24)) <> 0 Then 
                 '// Delete data that we do not need for each tab
                If myBaseRow.Cells.Item(1, 21) <> "Pending" Then 
                    myBaseRow.Delete 
                End If 
            End If 
        Next 
        Sheets("Declined Timesheets").Select 
        Set myBaseWorkSheet = ActiveWorkbook.ActiveSheet 
        Set myBaseRange = myBaseWorkSheet.Rows 
        For RowsCounter = myBaseRange.Rows.Count To 2 Step -1 
            Set myBaseRow = myBaseRange.Item(RowsCounter) 
            If Len(myBaseRow.Cells.Item(1, 24)) <> 0 Then 
                 '// Delete data that we do not need for each tab
                If myBaseRow.Cells.Item(1, 21) <> "Declined" Then 
                    myBaseRow.Delete 
                End If 
            End If 
        Next 
         '// Create Approved Timesheets Pivot
        ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _ 
        SourceData:="'Approved Timesheets'!A:AU").CreatePivotTable _ 
        TableDestination:="'Approved Timesheets Pivot'!R3C1", _ 
        TableName:="PivotTable1", _ 
        DefaultVersion:=xlPivotTableVersion10 
        Sheets("Approved Timesheets Pivot").Select 
        With ActiveSheet.PivotTables("PivotTable1") 
             
            PTSubtotals .PivotFields("Order ID") 
            PTSubtotals .PivotFields("Timesheet ID") 
            PTSubtotals .PivotFields("X-Ref PO ID") 
            PTSubtotals .PivotFields("Cost Center") 
            PTSubtotals .PivotFields("Contingent Staff First Name") 
            PTSubtotals .PivotFields("Contingent Staff Last Name") 
            PTSubtotals .PivotFields("Standard Rate") 
            PTSubtotals .PivotFields("Overtime Rate") 
            PTSubtotals .PivotFields("Second Overtime Rate") 
            PTSubtotals .PivotFields("Regular Hours") 
            PTSubtotals .PivotFields("Total Overtime Hours") 
            PTSubtotals .PivotFields("Total Second Overtime Hours") 
            PTSubtotals .PivotFields("Timesheet For Week Ending") 
            PTSubtotals .PivotFields("Total Amount") 
             
            ActiveSheet.PivotTables("PivotTable1").PivotFields("Order ID").PivotItems _ 
            ("(blank)").Visible = False 
             
            .AddFields RowFields:=Array("Order ID", "X-Ref PO ID", "Cost Center", _ 
            "Contingent Staff First Name", "Contingent Staff Last Name", _ 
            "Timesheet ID", "Timesheet For Week Ending", "Regular Hours", _ 
            "Standard Rate", "Total Overtime Hours", "Overtime Rate", _ 
            "Total Second Overtime Hours", "Second Overtime Rate", "Total Amount") 
            .PivotFields("Timesheet ID").Orientation = xlDataField 
        End With 
         
        With Sheets("Approved Timesheets Pivot") 
             
            With .Rows("4:4") 
                .HorizontalAlignment = xlGeneral 
                .VerticalAlignment = xlBottom 
                .WrapText = True 
                .Orientation = 0 
                .AddIndent = False 
                .IndentLevel = 0 
                .ShrinkToFit = False 
                .ReadingOrder = xlContext 
                .MergeCells = False 
            End With 
            .Range("D:D,E:E").ColumnWidth = 9.71 
            .Columns("F:F").ColumnWidth = 9.43 
            .Range("I:I,K:K,M:M,N:N").NumberFormat = _ 
            "_-[$£-809]* #,##0.00_-;-[$£-809]* #,##0.00_-;_-[$£-809]* ""-""??_-;_-@_-" 
            .Columns("A:A").ColumnWidth = 10.57 
            .Columns("B:B").ColumnWidth = 10.57 
            .Columns("O:O").Hidden = True 
        End With 
         
         
         ''// Create Pending Timesheets Pivot
        ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _ 
        SourceData:="'Pending Timesheets'!A:AU").CreatePivotTable _ 
        TableDestination:="'Pending Timesheets Pivot'!R3C1", _ 
        TableName:="PivotTable1", _ 
        DefaultVersion:=xlPivotTableVersion10 
        Sheets("Pending Timesheets Pivot").Select 
        With ActiveSheet.PivotTables("PivotTable1") 
             
            PTSubtotals .PivotFields("Order ID") 
            PTSubtotals .PivotFields("Timesheet ID") 
            PTSubtotals .PivotFields("X-Ref PO ID") 
            PTSubtotals .PivotFields("Cost Center") 
            PTSubtotals .PivotFields("Contingent Staff First Name") 
            PTSubtotals .PivotFields("Contingent Staff Last Name") 
            PTSubtotals .PivotFields("Standard Rate") 
            PTSubtotals .PivotFields("Overtime Rate") 
            PTSubtotals .PivotFields("Second Overtime Rate") 
            PTSubtotals .PivotFields("Regular Hours") 
            PTSubtotals .PivotFields("Total Overtime Hours") 
            PTSubtotals .PivotFields("Total Second Overtime Hours") 
            PTSubtotals .PivotFields("Timesheet For Week Ending") 
            PTSubtotals .PivotFields("Total Amount") 
             
            ActiveSheet.PivotTables("PivotTable1").PivotFields("Order ID").PivotItems _ 
            ("(blank)").Visible = False 
             
            .AddFields RowFields:=Array("Order ID", "X-Ref PO ID", "Cost Center", _ 
            "Contingent Staff First Name", "Contingent Staff Last Name", _ 
            "Timesheet ID", "Timesheet For Week Ending", "Regular Hours", _ 
            "Standard Rate", "Total Overtime Hours", "Overtime Rate", _ 
            "Total Second Overtime Hours", "Second Overtime Rate", "Total Amount") 
            .PivotFields("Timesheet ID").Orientation = xlDataField 
        End With 
         
        With Sheets("Pending Timesheets Pivot") 
             
            With .Rows("4:4") 
                .HorizontalAlignment = xlGeneral 
                .VerticalAlignment = xlBottom 
                .WrapText = True 
                .Orientation = 0 
                .AddIndent = False 
                .IndentLevel = 0 
                .ShrinkToFit = False 
                .ReadingOrder = xlContext 
                .MergeCells = False 
            End With 
            .Range("D:D,E:E").ColumnWidth = 9.71 
            .Columns("F:F").ColumnWidth = 9.43 
            .Range("I:I,K:K,M:M,N:N").NumberFormat = _ 
            "_-[$£-809]* #,##0.00_-;-[$£-809]* #,##0.00_-;_-[$£-809]* ""-""??_-;_-@_-" 
            .Columns("A:A").ColumnWidth = 10.57 
            .Columns("B:B").ColumnWidth = 10.57 
            .Columns("O:O").Hidden = True 
        End With 
         '// Create Declined Timesheets Pivot
         
        ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _ 
        SourceData:="'Declined Timesheets'!A:AU").CreatePivotTable _ 
        TableDestination:="'Declined Timesheets Pivot'!R3C1", _ 
        TableName:="PivotTable1", _ 
        DefaultVersion:=xlPivotTableVersion10 
        Sheets("Declined Timesheets Pivot").Select 
        With ActiveSheet.PivotTables("PivotTable1") 
             
            PTSubtotals .PivotFields("Order ID") 
            PTSubtotals .PivotFields("Timesheet ID") 
            PTSubtotals .PivotFields("X-Ref PO ID") 
            PTSubtotals .PivotFields("Cost Center") 
            PTSubtotals .PivotFields("Contingent Staff First Name") 
            PTSubtotals .PivotFields("Contingent Staff Last Name") 
            PTSubtotals .PivotFields("Standard Rate") 
            PTSubtotals .PivotFields("Overtime Rate") 
            PTSubtotals .PivotFields("Second Overtime Rate") 
            PTSubtotals .PivotFields("Regular Hours") 
            PTSubtotals .PivotFields("Total Overtime Hours") 
            PTSubtotals .PivotFields("Total Second Overtime Hours") 
            PTSubtotals .PivotFields("Timesheet For Week Ending") 
            PTSubtotals .PivotFields("Total Amount") 
             
            ActiveSheet.PivotTables("PivotTable1").PivotFields("Order ID").PivotItems _ 
            ("(blank)").Visible = False 
             
            .AddFields RowFields:=Array("Order ID", "X-Ref PO ID", "Cost Center", _ 
            "Contingent Staff First Name", "Contingent Staff Last Name", _ 
            "Timesheet ID", "Timesheet For Week Ending", "Regular Hours", _ 
            "Standard Rate", "Total Overtime Hours", "Overtime Rate", _ 
            "Total Second Overtime Hours", "Second Overtime Rate", "Total Amount") 
            .PivotFields("Timesheet ID").Orientation = xlDataField 
        End With 
         
        With Sheets("Declined Timesheets Pivot") 
             
            With .Rows("4:4") 
                .HorizontalAlignment = xlGeneral 
                .VerticalAlignment = xlBottom 
                .WrapText = True 
                .Orientation = 0 
                .AddIndent = False 
                .IndentLevel = 0 
                .ShrinkToFit = False 
                .ReadingOrder = xlContext 
                .MergeCells = False 
            End With 
            .Range("D:D,E:E").ColumnWidth = 9.71 
            .Columns("F:F").ColumnWidth = 9.43 
            .Range("I:I,K:K,M:M,N:N").NumberFormat = _ 
            "_-[$£-809]* #,##0.00_-;-[$£-809]* #,##0.00_-;_-[$£-809]* ""-""??_-;_-@_-" 
            .Columns("A:A").ColumnWidth = 10.57 
            .Columns("B:B").ColumnWidth = 10.57 
            .Columns("O:O").Hidden = True 
        End With 
         
        Application.ScreenUpdating = True 
         
    End Sub
Unable to upload example sheet here, but I can provide a link if required

Thanks,

Marshybid


 
Sort by date Sort by votes
maybe myBaseRow.Cells.Item(1, 44)[red].value[/red]?

_________________
Bob Rashkin
 
Upvote 0 Downvote



Hi,
Code: 
With myBaseRow.Cells
    If .Item(1, 21) <> "Approved" Then[b]
        If .Item(1, 44) <= StartDate And .Item(1, 44) >= EndDate Then[/b]
            
        End If
    End If
End With
it ALL depends on the values of StartDate and EndDate.
Code: 
    Dim StartDate As Date 
    Dim EndDate As Date 
    StartDate =  Format(Date, "mm/dd/yyyy") 
    EndDate = Format(Date, "mm/dd/yyyy")
Do you see a problem here? They have IDENTICAL values.

You actually have another minor error. The FORMAT function returns a STRING and not a DATE. It LOOKS like a Date but it is NOT.

So here's what happens on this statement...
Code: 
    StartDate =  Format(Date, "mm/dd/yyyy")
Today's date, which, right now, is 39568, is CONVERTED to a STRING representation of the date in the specified format by the Format function. THEN, because you have declared StartDate as a DATE variable, VB converts the STRING ("04/30/2008") back to a DATE 39568, and stuffs it into StartDate: an unnecessary statement!

Solution: Assign EndDate with a valid date that is greater than StartDate, and get fid of the Format functions.




Skip,

[glasses]Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![tongue]
 
Upvote 0 Downvote
Thanks Skip I made the changes you suggested and it seems to be working fine now.

Am I able to mark this thread as closed (solved)?

Marshybid

:eek:)
 
Upvote 0 Downvote




That's fine. Glad you got a solution.

Skip,

[glasses]Just got a nuance...
to replace the old subtlety![tongue]
 
Upvote 0 Downvote
Marshybid,

Tek-Tips doesn't have a way for the OP to "close" out a thread. The thread is left open in case other members want to suggest alternate solutions and to give you an opportunity to try out the information and come back if you need clarification. After a period of several months, the thread will automatically be closed in order to keep folks from reviving a really old question.

The "local" way to indicate that someone provided helpful information is to press the link at the bottom left of a post that reads,
[tab]"
star.gif
Thank [UserName] for this valuable post!".

That will award a little purple star to whichever member(s) you feel helped you out.

It also is a signal for future visitors that the thread contains helpful information. So if I searched the site for a problem and found three threads, I might start with the thread that has the most stars.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Solved
Combine two working macros into one for MS Word 1
Replies
7
Views
941
strongm
strongm
remeng
  • Locked
  • Question
multiple err handling
Replies
6
Views
603
remeng
remeng
morechocolate
  • Locked
  • Question
run-tune error 3704 operation is not allowed when the object is codes: I don't think so.
Replies
7
Views
1K
morechocolate
morechocolate
Kymenglish
  • Locked
  • Question
Automated data entry code in MS Excel VB - Edge code how to hit enter to remove popups
Replies
3
Views
415
strongm
strongm
sal21
  • Locked
  • Question
scrping in listbox in a web page
Replies
0
Views
475
sal21
sal21

Part and Inventory Search

Sponsor

Back
Top