Format Excel sheet with last row having sum and percentage formulas in the columns

This is the second part of the orginial question titled:   Format Excel sheet with a row suming the columns

The solution provided did what I asked, sum the columns on the last row.  But I forgot that 4 columns required a different formula and format (percentages).  The percentage columns (F:I) use this formula accordingly: =IF(C8=0,0,C8/B8)

Need help updating the code to incorporate the percentage formula into the last row and also format it as a percentage (100.00%).

``````Sub PostProcessing()
Dim MainWorksheet As Worksheet
' Make sure your worksheet name matches!
Set MainWorksheet = ActiveWorkbook.Worksheets("Have")
Dim DataRange As Range
Dim lastRow As Long
Set DataRange = MainWorksheet.UsedRange
' Now that you have the data in DataRange you can process it.

mainworksheet.range("A1:J1").Font.Bold = True
mainworksheet.range("A1:J1").Interior.ColorIndex = 15

With MainWorksheet
.Rows(1).Insert

With .Rows(2)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With

With .Range("B1:J1")
.Merge
.HorizontalAlignment = xlCenter
.Font.Bold = True
.FormulaR1C1 = "Test Execution Sprint View"
.Font.Name = "Arial"
.Font.Size = 12
End With

.[a2] = "Cycles"

lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

.Cells(lastRow, 1) = "Release Grand Total"
.Cells(lastRow, 2).Resize(1, 9).FormulaR1C1 = "=sum(R3C:R[-1]C)"

With .Cells(lastRow, 1).Resize(1, 10).Interior
.Pattern = xlSolid
.ColorIndex = 37
End With

End With

End Sub
``````
Execution-20Status-20by-20Sprint.xls
Who is Participating?

x

Commented:
My appologies, I thought thats what you wanted.

I'm a bit slow.

Here it is with the functions in the last row as well.

Regards,
Kelvin
``````Sub PostProcessing()
Dim MainWorksheet As Worksheet
' Make sure your worksheet name matches!
Set MainWorksheet = ActiveWorkbook.Worksheets("Have")
Dim DataRange As Range
Dim lastRow As Long
Set DataRange = MainWorksheet.UsedRange
' Now that you have the data in DataRange you can process it.

MainWorksheet.Range("A1:J1").Font.Bold = True
MainWorksheet.Range("A1:J1").Interior.ColorIndex = 15

With MainWorksheet
.Rows(1).Insert

With .Rows(2)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With

With .Range("B1:J1")
.Merge
.HorizontalAlignment = xlCenter
.Font.Bold = True
.FormulaR1C1 = "Test Execution Sprint View"
.Font.Name = "Arial"
.Font.Size = 12
End With

.[a2] = "Cycles"

lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

.Cells(lastRow, 1) = "Release Grand Total"
.Cells(lastRow, 2).Resize(1, 9).FormulaR1C1 = "=sum(R3C:R[-1]C)"
'/ % executed
.Range(.Cells(3, 6), .Cells(lastRow, 6)).FormulaR1C1 = "=IF(RC3=0, 0, RC3/RC2)"
'/ % pass of executed
.Range(.Cells(3, 7), .Cells(lastRow, 7)).FormulaR1C1 = "=IF(RC4=0, 0, RC4/RC3)"

'/ % fail of executed
.Range(.Cells(3, 8), .Cells(lastRow, 8)).FormulaR1C1 = "=IF(RC5=0, 0, RC5/RC3)"

'/ % pass of total
.Range(.Cells(3, 9), .Cells(lastRow, 9)).FormulaR1C1 = "=IF(RC4=0, 0, RC4/RC2)"

.Range(.Cells(3, 6), .Cells(lastRow, 9)).Style = "Percent"

'.last

With .Cells(lastRow, 1).Resize(1, 10).Interior
.Pattern = xlSolid
.ColorIndex = 37
End With

Set DataRange = MainWorksheet.UsedRange
With DataRange.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With DataRange.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With DataRange.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With DataRange.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With DataRange.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With DataRange.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With

End Sub
``````
0

Commented:
Sorry,

I'd like to help but am a bit confused.  I can't find your other questions for some reason (Haven't used EE in a while most likely).

Could you tell me:
" The percentage columns (F:I) use this formula accordingly: =IF(C8=0,0,C8/B8)"  What cell is that going in?

If you put that into the last row (assuming 8 in this case) of column F you want it to give you C/B?

0

Commented:
DOH just saw attachment, disregard please.
0

Commented:
I believe this will do what you want.

Regards,
Kelvin

``````Sub PostProcessing()
Dim MainWorksheet As Worksheet
' Make sure your worksheet name matches!
Set MainWorksheet = ActiveWorkbook.Worksheets("Have")
Dim DataRange As Range
Dim lastRow As Long
Set DataRange = MainWorksheet.UsedRange
' Now that you have the data in DataRange you can process it.

MainWorksheet.Range("A1:J1").Font.Bold = True
MainWorksheet.Range("A1:J1").Interior.ColorIndex = 15

With MainWorksheet
.Rows(1).Insert

With .Rows(2)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With

With .Range("B1:J1")
.Merge
.HorizontalAlignment = xlCenter
.Font.Bold = True
.FormulaR1C1 = "Test Execution Sprint View"
.Font.Name = "Arial"
.Font.Size = 12
End With

.[a2] = "Cycles"

lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

.Cells(lastRow, 1) = "Release Grand Total"
.Cells(lastRow, 2).Resize(1, 9).FormulaR1C1 = "=sum(R3C:R[-1]C)"

.Range(.Cells(3, 6), .Cells(lastRow - 1, 9)).FormulaR1C1 = "=IF(RC3=3, 0, RC3/RC2)"
.Range(.Cells(3, 6), .Cells(lastRow, 9)).Style = "Percent"

'.last

With .Cells(lastRow, 1).Resize(1, 10).Interior
.Pattern = xlSolid
.ColorIndex = 37
End With

Set DataRange = MainWorksheet.UsedRange
With DataRange.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With DataRange.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With DataRange.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With DataRange.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With DataRange.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With DataRange.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With

End Sub
``````
0

Commented:
Ugh, I'm really sorry for all of the posts.  I'm a bit rusty.

I made a mistake in the formulas.  Here's a corrected version.
-K
``````Sub PostProcessing()
Dim MainWorksheet As Worksheet
' Make sure your worksheet name matches!
Set MainWorksheet = ActiveWorkbook.Worksheets("Have")
Dim DataRange As Range
Dim lastRow As Long
Set DataRange = MainWorksheet.UsedRange
' Now that you have the data in DataRange you can process it.

MainWorksheet.Range("A1:J1").Font.Bold = True
MainWorksheet.Range("A1:J1").Interior.ColorIndex = 15

With MainWorksheet
.Rows(1).Insert

With .Rows(2)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With

With .Range("B1:J1")
.Merge
.HorizontalAlignment = xlCenter
.Font.Bold = True
.FormulaR1C1 = "Test Execution Sprint View"
.Font.Name = "Arial"
.Font.Size = 12
End With

.[a2] = "Cycles"

lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

.Cells(lastRow, 1) = "Release Grand Total"
.Cells(lastRow, 2).Resize(1, 9).FormulaR1C1 = "=sum(R3C:R[-1]C)"
'/ % executed
.Range(.Cells(3, 6), .Cells(lastRow - 1, 6)).FormulaR1C1 = "=IF(RC3=0, 0, RC3/RC2)"
'/ % pass of executed
.Range(.Cells(3, 7), .Cells(lastRow - 1, 7)).FormulaR1C1 = "=IF(RC4=0, 0, RC4/RC3)"

'/ % fail of executed
.Range(.Cells(3, 8), .Cells(lastRow - 1, 8)).FormulaR1C1 = "=IF(RC5=0, 0, RC5/RC3)"

'/ % pass of total
.Range(.Cells(3, 9), .Cells(lastRow - 1, 9)).FormulaR1C1 = "=IF(RC4=0, 0, RC4/RC2)"

.Range(.Cells(3, 6), .Cells(lastRow, 9)).Style = "Percent"

'.last

With .Cells(lastRow, 1).Resize(1, 10).Interior
.Pattern = xlSolid
.ColorIndex = 37
End With

Set DataRange = MainWorksheet.UsedRange
With DataRange.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With DataRange.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With DataRange.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With DataRange.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With DataRange.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With DataRange.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With

End Sub
``````
0

Author Commented:
It's still putting the sum formula in the last row in each column.
0

Author Commented:
Perfect!  Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.