• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Nested if statements

Y

ysherriff

Member
I am trying to write a nested if statement where a user select a timeframe and then the workbook is filtered based on the timeframe and copied to another workbook. Everything works fine except for some reason if the selection is "All Months, 2016 or 2017", then it skips the section where it copies to the other workbook. I am trying to see where i am missing the syntax. Below is part of the code with the nested if and attached is the full workbook with the entire code.

Thank you for your help.

Code: 
        'select usedrange to copy
        If Range("b7") = vbNullString Then 'it is blank
            dataWB.Close SaveChanges:=False
         ElseIf timeframe = "All Months" Then
             With ActiveSheet
               .Unprotect "ops"
               .AutoFilterMode = False
               .Columns("B:Z").EntireColumn.Hidden = False
               .ListObjects("CJR_TBL").Range.AutoFilter Field:=1, Criteria1:="<>"
             End With
             
          ElseIf timeframe = "2016" Then
             With ActiveSheet
               .Unprotect "ops"
               .AutoFilterMode = False
               .Columns("B:Y").EntireColumn.Hidden = False
               .ListObjects("CJR_TBL").Range.AutoFilter Field:=2, Criteria1:=timeframe
             End With
         
          ElseIf timeframe = "2017" Then
             With ActiveSheet
               .Unprotect "ops"
               .AutoFilterMode = False
               .Columns("B:Y").EntireColumn.Hidden = False
               .ListObjects("CJR_TBL").Range.AutoFilter Field:=2, Criteria1:=timeframe
             End With
            Else
              With ActiveSheet
               .Unprotect "ops"
               .AutoFilterMode = False
               .Columns("B:Y").EntireColumn.Hidden = False
               .ListObjects("CJR_TBL").Range.AutoFilter Field:=1, Criteria1:=timeframe
             End With
         
           'copy filtered data
            Set tbl = ActiveSheet.AutoFilter.Range
            tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Select
            Selection.Copy
             
             'activate generator workbook
               currentWB.Activate
               
               'activate master worksheet
               TargetSh.Activate
                               
               TargetSh.Range(DestCell.Address).Select
               Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                       :=False, Transpose:=False
              Set DestCell = TargetSh.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
              dataWB.Close False
          End If
    Next i
       
    Application.StatusBar = False
    ProgressBox.Hide
    currentWB.Activate
    Sheets("Generate").Activate
    MsgBox "Reports have been generated successfully!", vbInformation
    Sheets("CJR Tracking").Activate
   
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
   
 End Sub
 

Attachments

  • REGION 1 DOR CJR REPORT.xlsm
    294 KB · Views: 2
Thanks Marc. I understand "All Months, 2016 or 2017" is not in the code. I meant to say if the selection is All Months or 2016 or 2017. These are individual selections in a dropdown list.

Hope that helps
 
In the case of All months, just clear the filter. Criteria may not be <>.
In case of 2016 or 2017. Try Disabling months filter and filter based on year.

Hope this helps.

with best regards
Arun N
 
Hi and thanks for the response. Let me clarify because i think i am not clear. From the image below, the user select which months, year, etc. they would like to generate the reports for. The nested if statement works if the user selects the individual months but doesn't work if the user selects "All Months" or "2016" or "2017". It skips this section of the copy and paste code for some reason. see code below. I do not know why that is the case. it has something to do with the order of my nested if or how it is structured. I hope that helps.


upload_2016-10-2_16-3-17.png


Code: 
'copy filtered data
            Set tbl = ActiveSheet.AutoFilter.Range
            tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Select
            Selection.Copy
             
             'activate generator workbook
               currentWB.Activate
               
               'activate master worksheet
               TargetSh.Activate
                               
               TargetSh.Range(DestCell.Address).Select
               Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                       :=False, Transpose:=False
              Set DestCell = TargetSh.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
              dataWB.Close False
 
below is the full vba code

Code: 
Sub Consolidate_DOR()
    Dim strListSheet As String, sh As Worksheet, TargetSh As Worksheet
    Dim DestCell As Range, LastRow As Long, i As Integer, strFileNamePath As String
    Dim strFileName As String, currentWB As Workbook, dataWB As Workbook, filecount As Integer
    Dim prctProgress As Single, tbl As Range, timeframe As String, Table As ListObjects
   

    strListSheet = "Report File Path"
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    filecount = Range("FILE_COUNT_LEVEL") '  the number of files to consolidate
           
    On Error Resume Next
    Set TargetSh = Worksheets("DOR Summary")
    On Error GoTo 0
   
    Sheets("DOR Summary").Activate
   With ActiveSheet
        .Unprotect "ops"
        .Range("CJR_TBL").ClearContents
    End With

   
    Set DestCell = TargetSh.Range("A1")
    Set DestCell = DestCell.Offset(1, 0)
   
    'On Error GoTo ErrH
    Sheets(strListSheet).Activate
    Range("b2").Select
   
   
     'this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
    Set currentWB = ActiveWorkbook
    For i = 1 To filecount
             
         timeframe = Range("TIME_FRAME")
         strFileName = Range("strFileName").Offset(i, 0)
         strFileNamePath = Range("strFileName").Offset(i, 3)
         
                         
      'this displays the status in percentage value  in the progress bar of the PSR file being generated and the name of file
        'being generated
        ProgressBox.Show 'displays progress bar
        Application.StatusBar = "Generating " & strFileName & " Consolidation....." & i & " of " & filecount
       
         prctProgress = i / filecount * 100
       
        ProgressBox.Increment prctProgress, "Consolidating for " & timeframe & "-" & "" & strFileName & "- " & i & " out of " & filecount
                     
               
        Application.Workbooks.Open strFileNamePath, UpdateLinks:=False, ReadOnly:=True
        Set dataWB = ActiveWorkbook
             ActiveSheet.Unprotect "ops"
        Set sh = ActiveSheet
               
         Sheets("CJR Tracking").Activate
                     
        'select usedrange to copy
        If Range("b7") = vbNullString Then 'it is blank
            dataWB.Close SaveChanges:=False
         ElseIf timeframe = "All Months" Then
             With ActiveSheet
               .Unprotect "ops"
               .AutoFilterMode = False
               .Columns("B:Z").EntireColumn.Hidden = False
               .ListObjects("CJR_TBL").Range.AutoFilter Field:=1, Criteria1:="<>"
             End With
             
          ElseIf timeframe = "2016" Then
             With ActiveSheet
               .Unprotect "ops"
               .AutoFilterMode = False
               .Columns("B:Z").EntireColumn.Hidden = False
               .ListObjects("CJR_TBL").Range.AutoFilter Field:=2, Criteria1:=timeframe
             End With
         
          ElseIf timeframe = "2017" Then
             With ActiveSheet
               .Unprotect "ops"
               .AutoFilterMode = False
               .Columns("B:Z").EntireColumn.Hidden = False
               .ListObjects("CJR_TBL").Range.AutoFilter Field:=2, Criteria1:=timeframe
             End With
             
            Else
              With ActiveSheet
               .Unprotect "ops"
               .AutoFilterMode = False
               .Columns("B:Z").EntireColumn.Hidden = False
               .ListObjects("CJR_TBL").Range.AutoFilter Field:=1, Criteria1:=timeframe
             End With
         
           'copy filtered data
            Set tbl = ActiveSheet.AutoFilter.Range
            tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Select
            Selection.Copy
             
             'activate generator workbook
               currentWB.Activate
               
               'activate master worksheet
               TargetSh.Activate
                               
               TargetSh.Range(DestCell.Address).Select
               Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                       :=False, Transpose:=False
              Set DestCell = TargetSh.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
              dataWB.Close False
          End If
    Next i
       
    Application.StatusBar = False
    ProgressBox.Hide
    currentWB.Activate
    Sheets("Generate").Activate
    MsgBox "Reports have been generated successfully!", vbInformation
    Sheets("CJR Tracking").Activate
   
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
   
 End Sub
 

Progress in code in step by step mode via F8 key
checking variables in Locals window,
maybe a bad logic for timeframe variable value …
​
 
Observing the code, I have few observations,

When you choose 2016 or 2017, it is a number and what you are comparing is a string. Hence, if you choose those options, the related code will not run. store those values i.e., 2016 and 2017 as text in the sheet [Control].

When you choose "All months", the criteria should be "*" to represent everything and not "<>".

Further Range CJR_TBL starts at A2. Hence its seems first line will never be copied as per your code.

Hope this will help.

with best regards,

Arun N
 
Thank you guys. i fixed the syntax. i had the below code before the End If statement and therefore with the multiple nest statements, it did not compute. I replaced the code after the END If loop and it worked.

Code: 
 'copy filtered data
            Set tbl = ActiveSheet.AutoFilter.Range
            tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Select
            Selection.Copy
             
             'activate generator workbook
               currentWB.Activate
               
               'activate master worksheet
               TargetSh.Activate
                               
               TargetSh.Range(DestCell.Address).Select
               Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                       :=False, Transpose:=False
              Set DestCell = TargetSh.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
              dataWB.Close False
 
You must log in or register to reply here.
Back
Top