This is what I have to do to the data I receive. As it is a regular routine, I thought of creating a macro which will finish this work in seconds.
I am sending the original data file along with the data with calculation. The yellow data is the data caclulated manually below the end of the last row of "Gross" column and dragged till the R/o column. I will see that the columns are arranged the same way as the code but the rows may vary from 800 - 2000 lines. That is the most difficult part for me to create the macro. If I calculate in the 2000th row then there would be lot of blank rows.
Query for Calculation by macro.xlsm |
---|
|
---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA |
---|
1 | Original Data >>>>> | | | | | Gross | A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | R/o |
---|
2 | | | | | | 3090.00 | 254.00 | 719.00 | 794.00 | 845.00 | | | | | 6.35 | 6.35 | 43.14 | 43.14 | 71.46 | 71.46 | 118.30 | 118.30 | | | | | -0.50 |
---|
3 | | | | | | 1518.00 | 140.00 | 998.00 | | 198.00 | | | | | 3.50 | 3.50 | 59.88 | 59.88 | | | 27.72 | 27.72 | | | | | -0.20 |
---|
4 | | | | | | 1894.00 | 483.00 | | 504.00 | 619.00 | | | | | 12.08 | 12.08 | | | 45.36 | 45.36 | 86.66 | 86.66 | | | | | -0.19 |
---|
5 | | | | | | 1710.00 | 214.00 | 368.00 | | 838.00 | | | | | 5.35 | 5.35 | 22.08 | 22.08 | | | 117.32 | 117.32 | | | | | 0.50 |
---|
6 | | | | | | 1977.00 | 701.00 | 835.00 | 259.00 | | | | | | 17.53 | 17.53 | 50.10 | 50.10 | 23.31 | 23.31 | | | | | | | 0.13 |
---|
7 | | | | | | 1880.00 | | 537.00 | 180.00 | 833.00 | | | | | | | 32.22 | 32.22 | 16.20 | 16.20 | 116.62 | 116.62 | | | | | -0.08 |
---|
8 | | | | | | 1580.00 | 715.00 | | 702.00 | | | | | | 17.88 | 17.88 | | | 63.18 | 63.18 | | | | | | | 0.89 |
---|
9 | | | | | | 2307.00 | 526.00 | 409.00 | 931.00 | 155.00 | | | | | 13.15 | 13.15 | 24.54 | 24.54 | 83.79 | 83.79 | 21.70 | 21.70 | | | | | -0.36 |
---|
10 | | | | | | 977.00 | | | 828.00 | | | | | | | | | | 74.52 | 74.52 | | | | | | | -0.04 |
---|
11 | | | | | | 730.00 | | | | | | | 103.00 | 475.00 | | | | | | | | | | | 18.54 | 133.00 | 0.46 |
---|
12 | | | | | | 1222.00 | | | | | 434.00 | 684.00 | | | | | | | | | | | 21.70 | 82.08 | | | 0.22 |
---|
13 | | | | | | 945.00 | | | | | 900.00 | | | | | | | | | | | | 45.00 | | | | 0.00 |
---|
14 | | | | | | 1226.00 | | | | | 560.00 | 570.00 | | | | | | | | | | | 28.00 | 68.40 | | | -0.40 |
---|
15 | | | | | | 998.00 | | | | | | | | 780.00 | | | | | | | | | | | | 218.40 | -0.40 |
---|
16 | | | | | | 1797.00 | | | | | 719.00 | | | 814.00 | | | | | | | | | 35.95 | | | 227.92 | 0.13 |
---|
17 | | | | | | 887.00 | | | | | 395.00 | | | 369.00 | | | | | | | | | 19.75 | | | 103.32 | -0.07 |
---|
18 | | | | | | 2278.00 | | | | | 449.00 | 628.00 | | 862.00 | | | | | | | | | 22.45 | 75.36 | | 241.36 | -0.17 |
---|
|
---|
This is the macro I recorded while doing the calculation.
Option Explicit
Sub Test()
'
' Test Macro
'
'
Range("F1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.End(xlDown).Select
Range("F20").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
Range("F20").Select
Selection.AutoFill Destination:=Range("F20:AA20"), Type:=xlFillDefault
Range("F20:AA20").Select
Selection.Style = "Comma"
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Font.Bold = True
Range("G21").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*2.5%"
Range("H21").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*6%"
Range("I21").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*9%"
Range("J21").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*14%"
Range("K21").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*5%"
Range("L21").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*12%"
Range("M21").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*18%"
Range("N21").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*28%"
Range("G22").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C-R[-2]C[8]"
Range("H22").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C-R[-2]C[9]"
Range("I22").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C-R[-2]C[10]"
Range("J22").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C-R[-2]C[12]"
Range("K22").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C-R[-2]C[12]"
Range("L22").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C-R[-2]C[12]"
Range("M22").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C-R[-2]C[12]"
Range("N22").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C-R[-2]C[12]"
Range("G21:N22").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Font.Bold = True
Range("G21").Select
End Sub