Hi All
I am really having a hard time computing the correction rate from a pivot table.
Correction Rate is computed by adding up NA,Within 1 Day, Within 3 days and within 5 days divided by the Grand Total. However, if I have uncorrected items, my codes dont work. Uncorrected shows up in column B if there are uncorrected.
I have attached an image. hope you can see it.
Thank you
Sheets("Pivot").Select
Range("I4").Select
ActiveCell.FormulaR1C1 = "Correction Rate"
Range("I5").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-7],RC[-6],RC[-4],RC[-3],RC[-2])/RC[-1]"
Range("I6").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-7],RC[-6],RC[-4],RC[-3],RC[-2])/RC[-1]"
Range("I6").Select
Selection.AutoFill Destination:=Range("I6:I11"), Type:=xlFillDefault
Range("I6:I11").Select
Range("K11").Select
Columns("I:I").EntireColumn.AutoFit
Range("H4").Select
Selection.Copy
Range("I4").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Columns("I:I").EntireColumn.AutoFit
Range("I5:I11").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Style = "Percent"
Range("H11").Select
Selection.Copy
Range("I11").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Style = "Percent"
Range("J8").Select
Sheets("QA Coaching Data").Select
Application.ScreenUpdating = True
End Sub
I am really having a hard time computing the correction rate from a pivot table.
Correction Rate is computed by adding up NA,Within 1 Day, Within 3 days and within 5 days divided by the Grand Total. However, if I have uncorrected items, my codes dont work. Uncorrected shows up in column B if there are uncorrected.
I have attached an image. hope you can see it.
Thank you
Sheets("Pivot").Select
Range("I4").Select
ActiveCell.FormulaR1C1 = "Correction Rate"
Range("I5").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-7],RC[-6],RC[-4],RC[-3],RC[-2])/RC[-1]"
Range("I6").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-7],RC[-6],RC[-4],RC[-3],RC[-2])/RC[-1]"
Range("I6").Select
Selection.AutoFill Destination:=Range("I6:I11"), Type:=xlFillDefault
Range("I6:I11").Select
Range("K11").Select
Columns("I:I").EntireColumn.AutoFit
Range("H4").Select
Selection.Copy
Range("I4").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Columns("I:I").EntireColumn.AutoFit
Range("I5:I11").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Style = "Percent"
Range("H11").Select
Selection.Copy
Range("I11").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Style = "Percent"
Range("J8").Select
Sheets("QA Coaching Data").Select
Application.ScreenUpdating = True
End Sub
Last edited: