VBA code to sum from above 5 cells to below 5 cells and selection middle cell from drop down box of column list

[bullletExcel]

VBA code to sum from above 5 cells to below 5 cells and selection middle cell from drop down box of column list in excel

 

[kweaver]

Welcome to Mr. Excel.

It would help if you could use XL2BB and provide a few representative examples of your data and expected results

 

[bullletExcel]

test vba.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1					OI	STRIKE PRICE			OIC
2	NIFTY2171514700CE	#########	952.85	-63.9	1575	14700	20210715	CE	75
3	NIFTY2171514750CE	#########	0	0	75	14750	20210715	CE	0
4	NIFTY2171514800CE	#########	904.75	-7.85	0	14800	20210715	CE	0				Here, strike price is going select in the drop down box and get trigger to specific row of OI and OIC column
5	NIFTY2171514850CE	#########	880.05	-14.1	150	14850	20210715	CE	75
6	NIFTY2171514900CE	#########	0	0	225	14900	20210715	CE	75				Select Strike price here
7	NIFTY2171514950CE	#########	791.95	1.1	225	14950	20210715	CE	75
8	NIFTY2171515000CE	#########	703.75	-13.9	17925	15000	20210715	CE	-3075
9	NIFTY2171515050CE	#########	632.85	-18.3	3000	15050	20210715	CE	300
10	NIFTY2171515100CE	#########	609.9	-10.7	8325	15100	20210715	CE	-225
11	NIFTY2171515150CE	#########	518.25	-47.95	2625	15150	20210715	CE	-75
12	NIFTY2171515200CE	#########	505.1	-15.8	9000	15200	20210715	CE	-1650				here, sum from above 5 cells to below 5 cell of selected OI row
13	NIFTY2171515250CE	#########	449.8	-17.75	1350	15250	20210715	CE	-75
14	NIFTY2171515300CE	#########	404.65	-17.7	27750	15300	20210715	CE	5475				Sum of OI
15	NIFTY2171515350CE	#########	359.4	-13.3	3975	15350	20210715	CE	2100
16	NIFTY2171515400CE	#########	306.3	-19.8	84000	15400	20210715	CE	19575
17	NIFTY2171515450CE	#########	238.05	-41.05	34050	15450	20210715	CE	680				same as OI, but sum in OIC column
18	NIFTY2171515500CE	#########	215	-21.35	390675	15500	20210715	CE	47100
19	NIFTY2171515550CE	#########	173	-23.3	257775	15550	20210715	CE	-33825				Sum of OIC
20	NIFTY2171515600CE	#########	134.5	-22.9	1E+06	15600	20210715	CE	10992
21	NIFTY2171515650CE	#########	100	-23.85	513000	15650	20210715	CE	51675
22	NIFTY2171515700CE	#########	70	-24.15	5E+06	15700	20210715	CE	132750
23	NIFTY2171515750CE	#########	47.95	-21.8	3E+06	15750	20210715	CE	826575
24	NIFTY2171515800CE	#########	30.5	-19.75	7E+06	15800	20210715	CE	2E+06
25	NIFTY2171515850CE	#########	18	-17.4	4E+06	15850	20210715	CE	2E+06
26	NIFTY2171515900CE	#########	10.5	-13.75	4E+06	15900	20210715	CE	963600
27	NIFTY2171515950CE	#########	6.2	-10.15	1E+06	15950	20210715	CE	328950
28	NIFTY2171516000CE	#########	4.65	-6.6	4E+06	16000	20210715	CE	297300
29	NIFTY2171516050CE	#########	3.4	-4.25	980850	16050	20210715	CE	-4E+05
30	NIFTY2171516100CE	#########	3	-2.85	3E+06	16100	20210715	CE	34350
31	NIFTY2171516150CE	#########	2.6	-1.9	906300	16150	20210715	CE	135375
32	NIFTY2171516200CE	#########	2.5	-1.5	3E+06	16200	20210715	CE	-3E+05
33	NIFTY2171516250CE	#########	2.2	-1.25	463275	16250	20210715	CE	57225
34	NIFTY2171516300CE	#########	2	-1.15	4E+06	16300	20210715	CE	1E+06
35	NIFTY2171516350CE	#########	2.15	-0.75	234525	16350	20210715	CE	39750
36	NIFTY2171516400CE	#########	1.8	-0.85	2E+06	16400	20210715	CE	528075
37	NIFTY2171516450CE	#########	1.8	-0.7	99975	16450	20210715	CE	22275
38	NIFTY2171516500CE	#########	1.85	-0.5	4E+06	16500	20210715	CE	-81000
39	NIFTY2171516550CE	#########	1.9	-0.3	54825	16550	20210715	CE	10875
40	NIFTY2171516600CE	#########	1.75	-0.45	531675	16600	20210715	CE	99750
41	NIFTY2171516650CE	#########	1.75	-0.4	16650	16650	20210715	CE	6375
42
43
Sheet1

 

[kweaver]

Unfortunately, I don't understand your explanation.
Is there a drop down in cell M6 and one in M14, etc.? Your red highlighted cells.

If so, do you want the sum of the E column from 2 to 5 (there aren't 5 rows above M6) ?

What is being summed (ranges) and where is the answer being placed and how does Excel know which column to sum (OI or OIC header)?

 

[bullletExcel]

Unfortunately, I don't understand your explanation.
Is there a drop down in cell M6 and one in M14, etc.? Your red highlighted cells.

If so, do you want the sum of the E column from 2 to 5 (there aren't 5 rows above M6) ?

What is being summed (ranges) and where is the answer being placed and how does Excel know which column to sum (OI or OIC header)?

Drop down to select cell from strike price column,
Once strike price cell is selected then selected cell row is activated,
In selected row, Sum--- from above 5 cell to below 5 cell of OI and OIC COLUMN

 

[kweaver]

Does the drop down provide a ROW number or what? Would you provide a very specific example, please?

 

[bullletExcel]

Does the drop down provide a ROW number or what? Would you provide a very specific example, please?

Drop down box should be value or number of strike price column ( mark in yellow)

 

[kweaver]

Maybe someone else understands what you're trying to describe. I don't

I don't know from your replies what cell(s) contain the drop down and what then is selected from the drop down.
If a drop down has an option to select, say, 15550 from the F column, that could be first in F18 or second in F19. ?????
Then, where does the sum above and below get placed?

Why can't you provide a specific example of where the selection is being made and then what is being summed and where the result is being place? One or two examples.

 

[bullletExcel]

Drop down is for selecting a cell from F column, to activate the specific row...
For eg..
Lets say, Cell F19 strike price is selected from drop down and now row 19 is activated,
From this row, at OI or E COLUMN, from above 5 cells to below 5 cells will be adding or sum together.

 

[kweaver]

If row 19 is selected, then 5 cells above that row + 5 cells below that row sum to 1,71E+07. I don't see what that is on your sample data.