1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

not able to calculate formula to all rows

Discussion in 'VBA Macros' started by narsing rao, Feb 23, 2017.

  1. narsing rao

    narsing rao Member

    Messages:
    99
    Hi Team,

    as usual again i am facing some problem with macros which i have created when i am trying to apply formula to other rows in sheet its not happening.if you consider below table in L2 i am applying formula =j2-k2 and dragging it down till the data is available but for some cells formula is getting applied and for some its not happening because of this macros is creating wrong report.

    code is attached.


    upload_2017-2-23_16-53-53.png

    Code (vb):

    Sub Macrosaseve()
    '
    ' Macrosaseve Macro
    '

    '
       Cells.Select
        With Selection
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlLTR
            .MergeCells = False
        End With
        Columns("C:C").Select
        Selection.Delete Shift:=xlToLeft
        Columns("D:D").Select
        Selection.Delete Shift:=xlToLeft
        Columns("H:H").Select
        Selection.Delete Shift:=xlToLeft
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 4
        ActiveWindow.ScrollColumn = 5
        ActiveWindow.ScrollColumn = 6
        Columns("N:Q").Select
        Selection.Delete Shift:=xlToLeft
        Columns("P:V").Select
        Selection.Delete Shift:=xlToLeft
        ActiveWindow.ScrollColumn = 9
        ActiveWindow.ScrollColumn = 8
        ActiveWindow.ScrollColumn = 7
        ActiveWindow.ScrollColumn = 6
        ActiveWindow.ScrollColumn = 5
        ActiveWindow.ScrollColumn = 4
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 1
        Rows("1:1").Select
        Selection.AutoFilter
        Range("L1").Select
        ActiveCell.FormulaR1C1 = "sla hrs left"
        Range("L2").Select
        ActiveCell.FormulaR1C1 = "=RC:L10932J2-RC[-1]"
        Range("L2").Select
        ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
        Range("L2").Select
        Selection.AutoFill Destination:=Range("L2:L10932")
        Range("L2:L10932").Select
        ActiveWindow.ScrollRow = 53
        ActiveWindow.ScrollRow = 159
        ActiveWindow.ScrollRow = 901
        ActiveWindow.ScrollRow = 1033
        ActiveWindow.ScrollRow = 1748
        ActiveWindow.ScrollRow = 1987
        ActiveWindow.ScrollRow = 2596
        ActiveWindow.ScrollRow = 2807
        ActiveWindow.ScrollRow = 3920
        ActiveWindow.ScrollRow = 4184
        ActiveWindow.ScrollRow = 5641
        ActiveWindow.ScrollRow = 5800
        ActiveWindow.ScrollRow = 6594
        ActiveWindow.ScrollRow = 6753
        ActiveWindow.ScrollRow = 8024
        ActiveWindow.ScrollRow = 8236
        ActiveWindow.ScrollRow = 9984
        ActiveWindow.ScrollRow = 10169
        ActiveWindow.ScrollRow = 10725
        ActiveWindow.ScrollRow = 10778
        ActiveWindow.ScrollRow = 10911
        ActiveWindow.ScrollRow = 10831
        ActiveWindow.ScrollRow = 10460
        ActiveWindow.ScrollRow = 9851
        ActiveWindow.ScrollRow = 7998
        ActiveWindow.ScrollRow = 7547
        ActiveWindow.ScrollRow = 5747
        ActiveWindow.ScrollRow = 2675
        ActiveWindow.ScrollRow = 2251
        ActiveWindow.ScrollRow = 1510
        ActiveWindow.ScrollRow = 1404
        ActiveWindow.ScrollRow = 980
        ActiveWindow.ScrollRow = 742
        ActiveWindow.ScrollRow = 292
        ActiveWindow.ScrollRow = 212
        ActiveWindow.ScrollRow = 1
        ActiveSheet.Range("$A$1:$AD$10932").AutoFilter Field:=13, Criteria1:="Met"
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 4
        ActiveWindow.ScrollColumn = 5
        ActiveSheet.Range("$A$1:$AD$10932").AutoFilter Field:=14, Criteria1:= _
            "PRODUCTION"
        ActiveSheet.Range("$A$1:$AD$10932").AutoFilter Field:=15, Criteria1:=Array( _
            "ALL OTHERS", "AMLA", "APP-ISSUE", "CLARIFICATION", "CLARIFICATION_SOUGHT", _
            "Clarifications-Documentation not clear", _
            "Clarifications-Due to Finacle Knowledge", "Clarifications-Due to User Mistakes" _
            , "Clarifications-Gap in Solution", "Code Review", "Coding Error", "CR", _
            "Critical Patches", "Custom Localization", "DATA MIGRATION", _
            "Data Migration Error", "Data Related Issues-Data Mapping Issue", _
            "Data Related Issues-Incomplete Data", "Data Related Issues-Wrong Data", _
            "Deployment Error", "Design Error", "DESIGN ISSUES", "DOC_SUPPORT", "DOCUMENTATION" _
            , "DUPLICATE", "Enhancement", "EOD/BOD", "Essential Patches", "IMPLEMENTATION", _
            "IMPLEMENTATION OF NEW PRODUCTS", "Infrastructure Error", "In-Progress", _
            "INTERFACE", "Java UBPS Admin", "L2 Production suport", "Missing Requirement", _
            "NEW REQUIREMENT", "NOT AN ISSUE", "OBSERVATION", "OPERATING SYSTEM", "OTHERS", _
            "Parameter Error", "PARAMETERIZATION ISSUES", _
            "Parameterization Issues-New Requirement", _
            "Parameterization Issues-Operational Parameters", _
            "Parameterization Issues-Wrong Parameterization", "PERFORMANCE", "Porting", _
            "PRODUCT BUG", "Product Bug-Bug in the Product", "Product Bug-Performance Issue", _
            "Product Bug-Small change in the Product", "Product Dependency", _
            "Product Feature", "Product Localization", "Production", "Recon", _
            "Regression Testing", "RELEASE DEPENDENT", "REPORT", "SCHEDULING_REVIEW", _
            "SERVICE RESOLVER", "SET UP ISSUES", "SIT_BUG", _
            "SOFTWARE ERROR/TROUBLE FIX REQUEST", "STR", _
            "Technical Issue-Configuration Issue", "Technical Issue-EOD BOD Issues", "Test", _
            "Test Data Error", "UBPS-Product"), Operator:=xlFilterValues
        ActiveWorkbook.Worksheets("GetSLACreateReportTOLPlus").AutoFilter.Sort. _
            SortFields.Clear
        ActiveWorkbook.Worksheets("GetSLACreateReportTOLPlus").AutoFilter.Sort. _
            SortFields.Add Key:=Range("L1:L10932"), SortOn:=xlSortOnValues, Order:= _
            xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("GetSLACreateReportTOLPlus").AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        ActiveWindow.ScrollColumn = 4
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 1
        Range("A1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy
        Sheets.Add After:=ActiveSheet
        ActiveSheet.Paste
        Rows("1:1").Select
        Application.CutCopyMode = False
        Selection.AutoFilter
        ActiveSheet.Range("$A$1:$O$6027").AutoFilter Field:=12, Criteria1:=">=0", _
            Operator:=xlAnd, Criteria2:="<=48"
        ActiveSheet.Range("$A$1:$O$6027").AutoFilter Field:=8, Criteria1:=Array( _
            "With Assignee", "With CCB Approver", "With Requestor For Clarification"), _
            Operator:=xlFilterValues
        Range("A1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy
        Sheets.Add After:=ActiveSheet
        ActiveSheet.Paste
        Rows("1:1").Select
        Application.CutCopyMode = False
        Selection.AutoFilter
        ActiveSheet.Range("$A$1:$O$21").AutoFilter Field:=8, Criteria1:= _
            "=With Assignee", Operator:=xlOr, Criteria2:="=With CCB Approver"
        Range("A1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy
        Sheets.Add After:=ActiveSheet
        ActiveSheet.Paste
        Sheets("Sheet2").Select
        Range("F27").Select
        Application.CutCopyMode = False
        Selection.AutoFilter
        Rows("1:1").Select
        Selection.AutoFilter
        ActiveSheet.Range("$A$1:$O$21").AutoFilter Field:=8, Criteria1:= _
            "With Requestor For Clarification"
        Range("A1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy
        Sheets.Add After:=ActiveSheet
        Range("A1").Select
        ActiveSheet.Paste
        Sheets("Sheet4").Select
        Sheets("Sheet4").Name = "open with bank"
        Sheets("Sheet3").Select
        Sheets("Sheet3").Name = "open with infy"
        Range("H15").Select
        Sheets("open with bank").Select
        Range("E11").Select
    End Sub
    note: code is created using macro.

    with me the problem is i cant attach copy of excel which i am working as it is prohibited to share official data.

    narsing rao
  2. PCosta87

    PCosta87 Well-Known Member

    Messages:
    704
    Hi,

    Although the code could be optimized quite a bit, the main problem is probably not in the code itself.
    What you describe is consistent with having filters applied... if you drag down with filters applied it will only apply the formula to the visible cells.

    Try clearing all filters before running the code and it should work.

    If you want to add the code to clear the filters to your existing code, it should be something like:
    Code (vb):
    ActiveSheet.ShowAllData
    If it throws an error you may need to check if data is being filtered, or at least use:
    Code (vb):
    On Error Resume Next
    before the ShowAllData statement

    Hope this helps
    narsing rao likes this.
  3. narsing rao

    narsing rao Member

    Messages:
    99
    Hi Pcosta,

    thanks for your reply..i will check this...i need some solution to this problem

    1 . our data keep changing
    2. when i create a macro the macro select particular row and column according to that day report.
    3. when i pull the data next day the data will change ..when i run the macro with new data does it dynamically select the rows and columns according to new report ?? i have seen that in code it will show as perticular row and column no is this correct??

    now i need some code which select the row and column dynamically according to that day data.

    please let me know how to make this happen.

    Thanks in advance
  4. Derek McGill

    Derek McGill Member

    Messages:
    115
    The formula is filled into L2:L10932 correctly.
  5. PCosta87

    PCosta87 Well-Known Member

    Messages:
    704
    Hi,

    Indeed, when you use Macro recorder it will not be dynamic... it will record the exact rows and columns you selected.

    Usually, only the number of rows change (columns remain the same)... in that case, you just need to find the last row and use that instead of the actual row number.

    How to:
    Imagine you have data in "A1:D10" and the next day you have data in "A1:D15" and so on.
    In the code you should have a variable to store the index number of the last row in use (use Integer as the row index is always a whole number):
    Code (vb):
    Dim lrow As Integer
    Then, set lrow as the last used row (using one of the columns in the range. I will use "A" in this example):
    Code (vb):
    lrow = Sheets("sheet name here!").Cells(Rows.Count, "A").End(xlUp).Row
    This tells Excel to get the row index number from the last used cell. It goes from the last cell in column "A" up until it reaches a non empty cell (Similar to Ctrl+Shift+Up from last cell of column "A" (this will be the last used).

    Now, to select the range dynamically you can use:
    Code (vb):
    Sheets("sheet name here!").Range("A1:D" & lrow).Select
    With this, what it will do is select the Range("A1:D10") in the first case and the Range("A1:D15") in the second...

    These steps are the basics... now you can adapt to what you need (replacing ".select"):
    .copy "destination" (to copy to a given destination)
    .clearcontents (to clear the contents of the cells within the range)
    .delete (to delete the cells)
    etc.
  6. PCosta87

    PCosta87 Well-Known Member

    Messages:
    704
    Just a suggestion...

    Get in the habit of using Pivot Tables whenever possible... combined with Tables (which you can reference by name) as the source.
    A simple refresh does basically the same thing and it is much easier to work with.
  7. p45cal

    p45cal Well-Known Member

    Messages:
    663
    2 completely separate questions/notes:

    1. If your current technique of selecting row 1 then clicking the Autofilter button correctly identifies the whole range that you need to autofilter then you can use the sheet's Autofilter object's .range property to identify extents, which you can then use in several places:
    Code (vb):
    With activesheet
      .Rows("1:1").AutoFilter
      .Range("L1").Value = "sla hrs left"
      Intersect(.Columns("L"), .AutoFilter.Range, .AutoFilter.Range.Offset(1)).FormulaR1C1 = "=RC[-2]-RC[-1]"
    End With
    and:
    Code (vb):
    Activesheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy
    2. Now I've got to the stage:
    Sheets("Sheet2").Select
    where I cannot know which sheet this actually is; was it in existence before the macro started, or is it one of the newly added sheets? If the latter, which one?

    In the same vein, I will also need to know which Sheets3 and Sheets4 are

    This is where I'm at so far:
    Code (vb):

    Sub Macrosaseve()
    Set Sht = ActiveSheet
    With Sht
      With .Cells
      .WrapText = False
      .Orientation = 0
      .AddIndent = False
      .IndentLevel = 0
      .ShrinkToFit = False
      .ReadingOrder = xlLTR
      .MergeCells = False
      End With  '.cells
     .Range("C:C,E:E,J:J,Q:T,W:AC").Delete
      If .AutoFilter Is Nothing Then
      .Rows("1:1").AutoFilter  'it should start unfiltered.
     Else  'there is an Autofilter present:
     If .AutoFilter.FilterMode Then Sht.ShowAllData
      End If
      .Range("L1").Value = "sla hrs left"
      Intersect(.Columns("L"), .AutoFilter.Range, .AutoFilter.Range.Offset(1)).FormulaR1C1 = "=RC[-2]-RC[-1]"
      With .AutoFilter.Range
      .AutoFilter Field:=13, Criteria1:="stuff"
      .AutoFilter Field:=14, Criteria1:="PRODUCTION"
      .AutoFilter Field:=15, Criteria1:=Array("ALL OTHERS", "AMLA", "APP-ISSUE", "CLARIFICATION", "CLARIFICATION_SOUGHT", "Clarifications-Documentation not clear", "Clarifications-Due to Finacle Knowledge", "Clarifications-Due to User Mistakes", "Clarifications-Gap in Solution", "Code Review", "Coding Error", "CR", "Critical Patches", "Custom Localization", "DATA MIGRATION", "Data Migration Error", "Data Related Issues-Data Mapping Issue", "Data Related Issues-Incomplete Data", "Data Related Issues-Wrong Data", "Deployment Error", "Design Error", "DESIGN ISSUES", "DOC_SUPPORT", "DOCUMENTATION", "DUPLICATE", "Enhancement", "EOD/BOD", "Essential Patches", "IMPLEMENTATION", "IMPLEMENTATION OF NEW PRODUCTS", "Infrastructure Error", "In-Progress", "INTERFACE", "Java UBPS Admin", "L2 Production suport", "Missing Requirement", "NEW REQUIREMENT", "NOT AN ISSUE", "OBSERVATION", "OPERATING SYSTEM", "OTHERS", "Parameter Error", "PARAMETERIZATION ISSUES", _
      "Parameterization Issues-New Requirement", "Parameterization Issues-Operational Parameters", "Parameterization Issues-Wrong Parameterization", "PERFORMANCE", "Porting", "PRODUCT BUG", "Product Bug-Bug in the Product", "Product Bug-Performance Issue", "Product Bug-Small change in the Product", "Product Dependency", "Product Feature", "Product Localization", "Production", "Recon", "Regression Testing", "RELEASE DEPENDENT", "REPORT", "SCHEDULING_REVIEW", "SERVICE RESOLVER", "SET UP ISSUES", "SIT_BUG", "SOFTWARE ERROR/TROUBLE FIX REQUEST", "STR", "Technical Issue-Configuration Issue", "Technical Issue-EOD BOD Issues", "Test", "Test Data Error", "UBPS-Product"), Operator:=xlFilterValues
      End With  '.AutoFilter.Range
     With .AutoFilter.Sort
      .SortFields.Clear
      .SortFields.Add Key:=Range("L1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
      End With  '.AutoFilter.Sort
     Set NewSht1 = Sheets.Add(After:=Sht)
      .AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy NewSht1.Range("A1")
    End With  'Sht

    NewSht1.Rows("1:1").AutoFilter
    NewSht1.AutoFilter Field:=12, Criteria1:=">=0", Operator:=xlAnd, Criteria2:="<=48"
    NewSht1.AutoFilter Field:=8, Criteria1:=Array("With Assignee", "With CCB Approver", "With Requestor For Clarification"), Operator:=xlFilterValues
    Set NewSht2 = Sheets.Add(After:=NewSht1)
    NewSht1.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy NewSht2.Range("A1")
    Set NewSht3 = Sheets.Add(After:=NewSht2)
    NewSht2.Rows("1:1").AutoFilter Field:=8, Criteria1:="=With Assignee", Operator:=xlOr, Criteria2:="=With CCB Approver"
    NewSht2.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy NewSht3.Range("A1")
    'got to here. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    Sheets("Sheet2").Select
    Range("F27").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$O$21").AutoFilter Field:=8, Criteria1:="With Requestor For Clarification"
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("Sheet4").Select
    Sheets("Sheet4").Name = "open with bank"
    Sheets("Sheet3").Select
    Sheets("Sheet3").Name = "open with infy"
    Range("H15").Select
    Sheets("open with bank").Select
    Range("E11").Select
    End Sub
    Last edited: Feb 23, 2017
  8. p45cal

    p45cal Well-Known Member

    Messages:
    663
    I made a mistake with my previous post with lines like:
    .AutoFilter Field:=13, Criter…
    now corrected in the last message.

    This is mighty difficult to test without sample data.
    narsing rao, could you put something (a workbook) together which isn't sensitive but still realistic, to experiment with?
    Last edited: Feb 23, 2017
  9. narsing rao

    narsing rao Member

    Messages:
    99
    Hi Pcosta and p45call ,
    the report goes like this.
    after extracting the data we delete some of the columns which are not required and we fallow below steps.

    upload_2017-2-24_10-25-53.png .
    as mentioned "MAIN" sheet is sheet1 and line no 6 (2c) is sheet2

    i hope you got some idea.

    below is the raw data :there are columns from A to AC

    upload_2017-2-24_10-30-15.png

    upload_2017-2-24_10-30-44.png

    from sheet2 we prepare "Open with infy " and "open with bank " sheets
    Last edited: Feb 24, 2017
  10. narsing rao

    narsing rao Member

    Messages:
    99
    Hi Pcosta,,

    can you make changes in the code according to the check list which i have shared.

    Regards,
    Narsing Rao
  11. narsing rao

    narsing rao Member

    Messages:
    99
    Hi Pcosta,

    i have prepared macros again without filters and this time after completing macros at Selection.AutoFill Destination:=Range("L2:L10932")

    L10932 value i have increased to some junk like 33333 as i know that my data never cross this no. and replace the value at all places ..after changing it when i checked the report i am able to generate correct report....is this correct what i did....i know it might look stupidity :( to you but it worked ....what i did it right ..or wrong....
  12. PCosta87

    PCosta87 Well-Known Member

    Messages:
    704
    Hi,

    I see, so I was right about the filters :)

    About the change in range, if 10932 wasn't enough, and it will never cross 33333 then yes, that works too... the only "problem" is that it will fill cells that don't need any formula too (all cells from the last row with data to the 33333th row). Depending on the formula, this will increase the file size and possibly slow it down.

    What you could do is use one of the already mentioned techniques to find the appropriate range and use that so it will only fill down to the last used cell.
    See the other post's code for reference... I used column "B" there to detect the last row and fill down the Sr. No in "A" (FillSeries) and the formula in "Q" (FillDown) - last 2 lines before "Refresh All".
    narsing rao likes this.
  13. narsing rao

    narsing rao Member

    Messages:
    99
    Sure.. i will work on that...mean while i have around 10 more reports to make automated some are complex with all formulas i hope you will help me with that..

    Thanks once again for your prompt response.... may i know form which region your from ..why i am asking this is because.. i get reply from u after afternoon only... i think its GMT for u.... Thank u
  14. Marc L

    Marc L Excel Ninja

    Messages:
    2,905


    Western European Time (WET like Greenwich Mean Time) …​
  15. PCosta87

    PCosta87 Well-Known Member

    Messages:
    704
    I'm from Portugal
    Western European Time (WET) - GMT as @Marc L said
  16. p45cal

    p45cal Well-Known Member

    Messages:
    663
    Test this, but I have a lot of uncertainties:
    Code (vb):
    Sub Macrosaseve()
    'Step 3:
    Set Sht = ActiveSheet
    With Sht
      With .Cells
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlLTR
        .MergeCells = False
      End With  '.cells
     .Range("C:C,E:E,J:J,Q:T,W:AC").Delete
      If .AutoFilter Is Nothing Then
        .Rows("1:1").AutoFilter  'it should start unfiltered.
     Else  'there is an Autofilter present:
       If .AutoFilter.FilterMode Then Sht.ShowAllData
      End If
      'Step 4:
     .Range("L1").Value = "sla hrs left"
      Intersect(.Columns("L"), .AutoFilter.Range, .AutoFilter.Range.Offset(1)).FormulaR1C1 = "=RC[-2]-RC[-1]"
      With .AutoFilter.Range
        .AutoFilter Field:=13, Criteria1:="Met"
        .AutoFilter Field:=14, Criteria1:="PRODUCTION"
        .AutoFilter Field:=15, Criteria1:=Array("ALL OTHERS", "AMLA", "APP-ISSUE", "CLARIFICATION", "CLARIFICATION_SOUGHT", "Clarifications-Documentation not clear", "Clarifications-Due to Finacle Knowledge", "Clarifications-Due to User Mistakes", "Clarifications-Gap in Solution", "Code Review", "Coding Error", "CR", "Critical Patches", "Custom Localization", "DATA MIGRATION", "Data Migration Error", "Data Related Issues-Data Mapping Issue", "Data Related Issues-Incomplete Data", "Data Related Issues-Wrong Data", "Deployment Error", "Design Error", "DESIGN ISSUES", "DOC_SUPPORT", "DOCUMENTATION", "DUPLICATE", "Enhancement", "EOD/BOD", "Essential Patches", "IMPLEMENTATION", "IMPLEMENTATION OF NEW PRODUCTS", "Infrastructure Error", "In-Progress", "INTERFACE", "Java UBPS Admin", "L2 Production suport", "Missing Requirement", "NEW REQUIREMENT", "NOT AN ISSUE", "OBSERVATION", "OPERATING SYSTEM", "OTHERS", "Parameter Error", "PARAMETERIZATION ISSUES", _
                                                "Parameterization Issues-New Requirement", "Parameterization Issues-Operational Parameters", "Parameterization Issues-Wrong Parameterization", "PERFORMANCE", "Porting", "PRODUCT BUG", "Product Bug-Bug in the Product", "Product Bug-Performance Issue", "Product Bug-Small change in the Product", "Product Dependency", "Product Feature", "Product Localization", "Production", "Recon", "Regression Testing", "RELEASE DEPENDENT", "REPORT", "SCHEDULING_REVIEW", "SERVICE RESOLVER", "SET UP ISSUES", "SIT_BUG", "SOFTWARE ERROR/TROUBLE FIX REQUEST", "STR", "Technical Issue-Configuration Issue", "Technical Issue-EOD BOD Issues", "Test", "Test Data Error", "UBPS-Product"), Operator:=xlFilterValues
        '.AutoFilter Field:=15, Criteria1:="<>SER", Operator:=xlAnd, Criteria2:="<>Software Enhancement request"'instead of some the 70 parameters in the line above?
     End With  '.AutoFilter.Range
     With .AutoFilter.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("L1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
      End With  '.AutoFilter.Sort
     Set NewSht1 = Sheets.Add(After:=Sht)
      .AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy NewSht1.Range("A1")
    End With  'Sht
    'Step 6?:
    NewSht1.Rows("1:1").AutoFilter
    NewSht1.AutoFilter Field:=12, Criteria1:=">=0", Operator:=xlAnd, Criteria2:="<=48"
    NewSht1.AutoFilter Field:=8, Criteria1:=Array("With Assignee", "With CCB Approver", "With Requestor For Clarification"), Operator:=xlFilterValues 'should that be "With Reviewer For Clarification" as in Step 7?
    Set NewSht2 = Sheets.Add(After:=NewSht1) 'will be Open with InfoSys.
    NewSht1.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy NewSht2.Range("A1")
    Set NewSht3 = Sheets.Add(After:=NewSht2)
    NewSht2.Rows("1:1").AutoFilter Field:=8, Criteria1:="=With Assignee", Operator:=xlOr, Criteria2:="=With CCB Approver"
    NewSht2.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy NewSht3.Range("A1")

    Set mySht = Sheets("Sheet2")  'that's if you're sure there's always going to be a Sheet2 and that it's the right sheet.
    mySht.Range("A1").AutoFilter Field:=8, Criteria1:="With Requestor For Clarification"
    Set NewSht4 = Sheets.Add(After:=mySht)
    mySht.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy NewSht4.Range("A1")
    NewSht3.Name = "open with bank"  'not sure I'm renaming the right sheet.
    NewSht4.Name = "open with infy"  'not sure I'm renaming the right sheet.
    Application.Goto Sheets("open with bank").Range("E11")
    End Sub
    I won't do any more unless I have a realistic starting file to play with.
    Take a note of the comments in the code.
  17. narsing rao

    narsing rao Member

    Messages:
    99
    thanks P45cal i will test this too....mean while

    Pcosta i need some clarification.

    Code (vb):

    ActiveSheet.Range("$A$1:$O$18").AutoFilter Field:=8, Criteria1:=Array( _
            "With Account Manager For Severity Change", "With Assignee", "With CCB Approver", _
            "With CCB Approver After Patch Initiation"), Operator:=xlFilterValues
     
    in above code there are some conditions which may appear in some cases and may not appear in other case according to the day which we pull the report today only "with assignee" is there while preparing the macro, but it may change next day.i have add manually all condition in the macro but its giving error
    Code (vb):

    ActiveSheet.Range("$A$1:$AC$2115").AutoFilter Field:=8, Criteria1:= _
            "With Assignee","With CCB Approver","With CCB Approver After Patch Initiation",_
            "With Release Manager Team For RCD Approval"), Operator:=xlFilterValues
     
    why i dont understand

    original code is :
    Code (vb):

    Sub MacroDFSREPORT()
    '
    ' MacroDFSREPORT Macro
    '

    '
       Cells.Select
        With Selection
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlLTR
            .MergeCells = False
        End With
        Columns("C:C").Select
        Selection.Delete Shift:=xlToLeft
        Columns("D:D").Select
        Selection.Delete Shift:=xlToLeft
        Columns("H:H").Select
        Selection.Delete Shift:=xlToLeft
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 4
        ActiveWindow.ScrollColumn = 5
        Columns("N:Q").Select
        Selection.Delete Shift:=xlToLeft
        Columns("P:V").Select
        Selection.Delete Shift:=xlToLeft
        ActiveWindow.ScrollColumn = 9
        ActiveWindow.ScrollColumn = 8
        ActiveWindow.ScrollColumn = 7
        ActiveWindow.ScrollColumn = 6
        ActiveWindow.ScrollColumn = 5
        ActiveWindow.ScrollColumn = 4
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 1
        Range("L1").Select
        ActiveCell.FormulaR1C1 = "SLA HRS LEFT"
        Range("L2").Select
        ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
        Range("L2").Select
        Selection.AutoFill Destination:=Range("L2:L2115")
        Range("L2:L2115").Select
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 1
        ActiveWindow.ScrollColumn = 2
        Rows("1:1").Select
        Range("B1").Activate
        Selection.AutoFilter
        ActiveSheet.Range("$A$1:$AC$2115").AutoFilter Field:=13, Criteria1:="Met"
        ActiveSheet.Range("$A$1:$AC$2115").AutoFilter Field:=8, Criteria1:= _
            "With Requestor For Clarification"
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 4
        ActiveSheet.Range("$A$1:$AC$2115").AutoFilter Field:=12, Criteria1:=">=0", _
            Operator:=xlAnd, Criteria2:="<=48"
        Rows("1:2117").Select
        Range("D1").Activate
        Selection.Copy
        Sheets.Add After:=ActiveSheet
        ActiveSheet.Paste
        Range("A2").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = "No calls"
        Range("E14").Select
        Sheets("GetSLACreateReportTOLPlus").Select
        Selection.AutoFilter
        Rows("1:1").Select
        Range("D1").Activate
        Selection.AutoFilter
        ActiveSheet.Range("$A$1:$AC$2115").AutoFilter Field:=8, Criteria1:= _
            "With Requestor For Clarification"
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 4
        ActiveWindow.ScrollColumn = 5
        ActiveWindow.ScrollColumn = 6
        ActiveSheet.Range("$A$1:$AC$2115").AutoFilter Field:=13, Criteria1:="Met"
        ActiveSheet.Range("$A$1:$AC$2115").AutoFilter Field:=12, Criteria1:=">=48" _
            , Operator:=xlAnd, Criteria2:="<=72"
        Rows("1:2117").Select
        Range("F1").Activate
        Selection.Copy
        Sheets.Add After:=ActiveSheet
        ActiveSheet.Paste
        Range("A2").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = "No calls 48 -72"
        Range("D17").Select
        Sheets("GetSLACreateReportTOLPlus").Select
        Selection.AutoFilter
        ActiveWindow.ScrollColumn = 5
        ActiveWindow.ScrollColumn = 4
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 1
        Rows("1:1").Select
        Selection.AutoFilter
        ActiveSheet.Range("$A$1:$AC$2115").AutoFilter Field:=13, Criteria1:="Met"
        ActiveSheet.Range("$A$1:$AC$2115").AutoFilter Field:=8, Criteria1:= _
            "With Assignee"
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 4
        ActiveSheet.Range("$A$1:$AC$2115").AutoFilter Field:=13
        ActiveSheet.Range("$A$1:$AC$2115").AutoFilter Field:=12, Criteria1:=">=0", _
            Operator:=xlAnd, Criteria2:="<=48"
        Rows("1:2117").Select
        Range("D1").Activate
        Selection.Copy
        Sheets.Add After:=ActiveSheet
        ActiveSheet.Paste
        Range("A2").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = "No calls open with infy 0-48"
        Range("B7").Select
        Sheets("GetSLACreateReportTOLPlus").Select
        Selection.AutoFilter
        Rows("1:1").Select
        Range("D1").Activate
        Selection.AutoFilter
        ActiveSheet.Range("$A$1:$AC$2115").AutoFilter Field:=8, Criteria1:= _
            "With Assignee"
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 4
        ActiveWindow.ScrollColumn = 5
        ActiveSheet.Range("$A$1:$AC$2115").AutoFilter Field:=13
        ActiveSheet.Range("$A$1:$AC$2115").AutoFilter Field:=12, Criteria1:=">=48" _
            , Operator:=xlAnd, Criteria2:="<=72"
        ActiveWindow.ScrollColumn = 4
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 1
        Range("A2116").Select
        Rows("1:2116").Select
        Selection.Copy
        Sheets.Add After:=ActiveSheet
        ActiveSheet.Paste
        Range("A2").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = "No calls with Infy 48-72"
        Range("D9").Select
        Sheets("GetSLACreateReportTOLPlus").Select
        Selection.AutoFilter
        Rows("1:1").Select
        Selection.AutoFilter
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 4
        ActiveWindow.ScrollColumn = 5
        ActiveWindow.ScrollColumn = 6
        ActiveSheet.Range("$A$1:$AC$2115").AutoFilter Field:=13, Criteria1:= _
            "Not Met"
        ActiveWindow.ScrollColumn = 5
        ActiveWindow.ScrollColumn = 4
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 1
        ActiveWindow.ScrollColumn = 2
        ActiveSheet.Range("$A$1:$AC$2115").AutoFilter Field:=8, Criteria1:= _
            "With Requestor For Clarification"
        ActiveWindow.ScrollColumn = 1
        Rows("1:1986").Select
        Selection.Copy
        Sheets.Add After:=ActiveSheet
        Range("A1").Select
        ActiveSheet.Paste
        Sheets("Sheet5").Select
        Sheets("Sheet5").Name = "Not met open with bank"
        Range("G12").Select
        Sheets("GetSLACreateReportTOLPlus").Select
        Application.CutCopyMode = False
        Selection.AutoFilter
        Rows("1:1").Select
        Selection.AutoFilter
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 4
        ActiveWindow.ScrollColumn = 5
        ActiveSheet.Range("$A$1:$AC$2115").AutoFilter Field:=13, Criteria1:= _
            "Not Met"
        Sheets.Add After:=ActiveSheet
        Range("A1").Select
        ActiveCell.FormulaR1C1 = "open with infy not met"
        Range("C4").Select
        Sheets("GetSLACreateReportTOLPlus").Select
        ActiveWindow.SmallScroll Down:=-3
    End Sub
     
  18. PCosta87

    PCosta87 Well-Known Member

    Messages:
    704
    Hi,

    I suspect you don't have a header in column 8 ("H")... see if "H1" is empty.
  19. narsing rao

    narsing rao Member

    Messages:
    99
    Hi Pcosta,

    this is how it looks in h column, the drop down.

    upload_2017-2-24_17-52-27.png

    but all the conditions may not be there in daily reports it may change, but i want to add all the conditions as we dont know which day that call may appear.
  20. PCosta87

    PCosta87 Well-Known Member

    Messages:
    704
    Hi,

    I can't seem to replicate any error here...
    What is the exact error it is throwing you?
    The only thing I can think of is if you don't have the correct sheet active. Since it uses "ActiveSheet.Range" you must have that specific sheet selected before that part of the code runs.
    To debug, try selecting the sheet where you have the range you want to filter and then run just that part of the code.
  21. narsing rao

    narsing rao Member

    Messages:
    99
    Hi Pcosta,
    ActiveSheet.Range("$A$1:$AC$2115").AutoFilter Field:=8, Criteria1:= _
    "With Assignee"
    If you check the above line of code "with assignee" this condition was met but there are other condition also there which are not appearing in today's data because those kind of calls not raised by customers,as I have created macro today they are not included if tomorrow any other condion call comes this macro could able to recognise that call and report may be wrong....that is my concern ...how to add all condions ....for better understanding I will paste checklist for you on Monday...I know you won't check mails on weekend ;)

    Thanks in advance
  22. narsing rao

    narsing rao Member

    Messages:
    99
    Hi Pcosta,

    can you please check what went wrong in this code

    upload_2017-2-27_15-25-37.png

    i have added below lines which are not there

    Operator:=xlOr, Criteria3:= _
    "=With CCB Approver", Operator:=xlOr, Criteria4:= _
    "=With CCB Approver After Patch Initiation", Operator:=xlOr, Criteria5:= _
    "=With Reviewer For Clarification", Operator:=xlOr, Criteria6:= _
    "=WIP"
    because these conditions should be checked while preparing the report.these are not available in the drop down list but i have added.

    please help..
  23. PCosta87

    PCosta87 Well-Known Member

    Messages:
    704
    Hi,

    Sorry for the late response... I was away from my PC for the last 2 days and couldn't respond.

    I still can't figure out why it is not working for you:
    Capturar.JPG

    The above pic was taken just after running the code you see in the VBE.
    None of the criteria were met and it still worked so I'm pretty sure it isn't the extra criteria that is causing the problem.

    Is there no other way you can upload the file with some dummy data so I can look at it? I understand you can't do it from work but is it possible to do it from home?
  24. narsing rao

    narsing rao Member

    Messages:
    99
    Hi Pcosta,

    Thanks for the reply,,i have solved the problem for my self....and very happy that your back......now i have different problem

    Code (vb):

    ActiveSheet.Range("$A$1:$AE$4716").AutoFilter Field:=5, Criteria1:=Array( _
            "10.02.09", "10.1.09", "10.1.10", "10.1.9", "10.2", "10.2.05", "10.2.06", "10.2.09", _
            "10.2.10", "10.2.11", "10.2.12.01", "10.2.12.02", "10.2.13", "10.2.13 Lite", _
            "10.2.14", "10.2.15", "10.2.16", "10.2.17", "10.2.18", "10.3.0", "10.3.06", "10.3.4", _
            "10.3.8", "11", "11.1", "11.2", "6.2.06", "7.1", "9.2", "CRM 10.1.06", "CRM 10.1.08", _
            "CRM 10.1.09", "CRM 10.2.02", "CRM 10.2.02.1", "CRM 10.2.04", "CRM 10.2.05", _
            "CRM 10.2.08", "CRM 10.2.09", "CRM 10.2.13", "CRM 6.2.03", "CRM 6.2.06", "CRM 6.3", _
            "CRM 7.1", "CRM 9.2", "FIN 10.1.06.02", "FIN 10.1.09", "Fin 10.2.03", "FIN 10.2.09", _
            "FIN 10.2.11", "FIN 10.2.13", "FIN 10.2.14", "FIN 10.2.15", "FIN 10.2.16", _
            "FIN 10.4.2", "FIN 7.1", "FIN 9.6.07", "FINCRM 10.1.03", "FINCRM 10.1.06", _
            "FINCRM 10.1.08", "FINCRM 10.1.09", "FINCRM 10.1.10", "FINCRM 10.2.02", _
            "FINCRM 10.2.03", "FINCRM 10.2.04", "FINCRM 10.2.05", "FINCRM 10.2.06", _
            "FINCRM 10.2.07", "FINCRM 10.2.08", "FINCRM 10.2.09", "FINCRM 10.2.10", _
            "FINCRM 10.2.12", "FINCRM 10.2.13", "FINCRM 10.4", "FINCRM 9.2", "FINCRM 9.2.01"), _
            Operator:=xlFilterValues
     
    there is long list of versions in the code, but some versions are missing in today's report now what i want is i want macro to deselect any version which starts with FINCRM 10.3.x(x=any number) remaining all should be there irrespective have data,sameway below is the big list of bank names

    upload_2017-3-1_16-27-29.png

    (note: i have marked them black because i dont want to display them in blog.)

    "UjjivanSAS_FINIMPL", "VCB", "WEMA", "ZUNO_ATS"), Operator:=xlFilterValues


    ending with above lines what i want is irrespective of any number of names, it should deselect names prefixes with “ITL " and “URALSIB ”.

    in the same way i have other filters where i have to deselect some and select some other from drop down ....now my quation is how to do this irrespective of data because data keep changing..
  25. PCosta87

    PCosta87 Well-Known Member

    Messages:
    704
    Hi,

    To remove results starting with FINCRM 10.3. you can use:
    Code (vb):
    Criteria1:="<>FINCRM 10.3.*"
    EDIT:
    The same goes for results starting with ITL:
    Code (vb):
    Criteria1:="<>ITL*"
    or URALSIB:
    Code (vb):
    Criteria1:="<>URALSIB*"

Share This Page