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