macro to be edited.

[RAJESH1960]

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 by a macro shared by Glenn,

As the number of rows change in every sheet, I would like to edit the macro by writing a code in the beginning, when I keep the cursor in Cell F2, it should do down to the last empty cell offset by 2nd blank cell in column F and offset to right. If the calculation is done with the help of a new macro it will be very helpful.

I will make sure that the columns are arranged the same way as in the code but the rows may vary from 800 - 2000 lines.
Herewith, I am sharing both the data, the original one and the one to be run with the macro.

Partly solved 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
Original Data

Cell Formulas
Range	Formula
AA2:AA18	AA2	=F2-SUM(G2:Z2)

Partly solved 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	Data with Manual calculation>>>>>					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
19
20				Grand Total		27,016.00	3,033.00	3,866.00	4,198.00	3,488.00	3,457.00	1,882.00	103.00	3,300.00	75.83	75.83	231.96	231.96	377.82	377.82	488.32	488.32	172.85	225.84	18.54	924.00	-0.08
21							75.83	231.96	377.82	488.32	172.85	225.84	18.54	924.00
22							-	-	-	-	-	-	-	-
23
Data with Manual calculation

Cell Formulas
Range	Formula
AA2:AA18	AA2	=F2-SUM(G2:Z2)
F20:AA20	F20	=SUM(F2:F19)
G21	G21	=G20*2.5%
H21	H21	=H20*6%
I21	I21	=I20*9%
J21	J21	=J20*14%
K21	K21	=K20*5%
L21	L21	=L20*12%
M21	M21	=M20*18%
N21	N21	=N20*28%
G22	G22	=G21-O20
H22	H22	=H21-Q20
I22	I22	=I21-S20
J22:N22	J22	=J21-V20

 

[RAJESH1960]

Macro shared by Glenn only for the calculation in row 21 and 22.
Sub GST_Match()
'
' GST Macro
' Press control + small letter g to run the calculation.
'
' Keyboard Shortcut: Ctrl+g

Application.Calculation = xlCalculationManual
ActiveCell.FormulaR1C1 = "=R[-1]C*2.5%"
For ifill = 1 To 8
ActiveCell.Offset(1, ifill - 1).FormulaR1C1 = "=R[-1]C-R[-2]C[" & Application.Min(ifill, 5) + 7 & "]"
Next
ActiveCell.Offset(0, 1).FormulaR1C1 = "=R[-1]C*6%"
ActiveCell.Offset(0, 2).FormulaR1C1 = "=R[-1]C*9%"
ActiveCell.Offset(0, 3).FormulaR1C1 = "=R[-1]C*14%"
ActiveCell.Offset(0, 4).FormulaR1C1 = "=R[-1]C*5%"
ActiveCell.Offset(0, 5).FormulaR1C1 = "=R[-1]C*12%"
ActiveCell.Offset(0, 6).FormulaR1C1 = "=R[-1]C*18%"
ActiveCell.Offset(0, 7).FormulaR1C1 = "=R[-1]C*28%"
Application.Calculation = xlCalculationAutomatic
End Sub