Hello everyone,
I’ve code (thanks to Saurabhjaina211), that inserts blanc columns between columns with data and makes calculations. I still have to manually make calculations of the average value at the bottom of the columns with percentage values. I was wandering if it is possible to automatically calculate average percentage of the values of the alternating columns with data in percentage format. The number of rows as well as number of columns are flexible, alternating columns start from column E. Would be grateful for any help.
P.S. Please find sample of the table as an attachment to this post.
Dilshod
Code
- Sub TableResults()
- '....
- Dim colNo, colStart, colFinish, colStep As Long
- Dim rng2Insert As Range
- colStep = 2
- colStart = Application.Cells(1, 4).Column + 1
- colFinish = (ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column * 2) - colStart
- Application.ScreenUpdating = False
- Application.Calculation = xlCalculationManual
- For colNo = colStart To colFinish Step colStep
- ActiveSheet.Cells(1, colNo).EntireColumn.Insert
- '***New code inserted
- ActiveSheet.Cells(1, colNo) = "Change%"
- ActiveSheet.Columns(colNo).NumberFormat = "0.00%"
- ActiveSheet.Cells(2, colNo) = "=(RC[-1]-RC[colNo-2])/RC[colNo-2]"
- ActiveSheet.Cells(2, colNo).Columns(colNo).AutoFill
- '***
- Next
- '***New code inserted
- ActiveSheet.Cells(1, colNo) = "Change%"
- ActiveSheet.Columns(colNo).NumberFormat = "0.00%"
- '***
- Dim colFinish2 As Integer, totalRows As Integer
- colFinish2 = (ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column)
- totalRows = WorksheetFunction.CountA(ActiveSheet.Range("B:B"))
- On Error Resume Next
- 'Application.ScreenUpdating = False
- For colNo = 5 To colFinish Step 2
- With ActiveSheet
- For rowno = 2 To totalRows
- .Cells(rowno, colNo) = (.Cells(rowno, colNo - 1) - .Cells(rowno, 3)) / .Cells(rowno, 3)
- Next
- End With
- Next
- Application.ScreenUpdating = True
- Application.EnableEvents = True
- Application.DisplayStatusBar = True
- Application.DisplayAlerts = True
- Application.CutCopyMode = False
- End Sub