• 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.

not able to calculate formula to all rows

narsing rao

narsing rao

Member
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: 
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
 
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: 
ActiveSheet.ShowAllData

If it throws an error you may need to check if data is being filtered, or at least use:
Code: 
On Error Resume Next
before the ShowAllData statement

Hope this helps
 
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
 
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: 
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: 
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: 
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.
 
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.
 
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: 
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: 
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: 
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:
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:
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:
PCosta87 said:
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: 
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: 
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: 
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.
Click to expand...

Hi Pcosta,,

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

Regards,
Narsing Rao
 
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....
 
narsing rao said:
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....
Click to expand...
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".
 
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
 
...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...
Click to expand...

I'm from Portugal
Western European Time (WET) - GMT as @Marc L said
 
Test this, but I have a lot of uncertainties:
Code: 
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.
 
thanks P45cal i will test this too....mean while

Pcosta i need some clarification.

Code: 
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: 
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: 
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
 
Hi,

narsing rao said:
thanks P45cal i will test this too....mean while

Pcosta i need some clarification.

Code: 
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
Click to expand...

I suspect you don't have a header in column 8 ("H")... see if "H1" is empty.
 
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.
 
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.
 
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
 
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..
 
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?
 
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: 
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..
 
Hi,

To remove results starting with FINCRM 10.3. you can use:
Code: 
Criteria1:="<>FINCRM 10.3.*"

EDIT:
The same goes for results starting with ITL:
Code: 
Criteria1:="<>ITL*"

or URALSIB:
Code: 
Criteria1:="<>URALSIB*"
 
You must log in or register to reply here.
Back
Top