Help needed for a macro

[hsandeep]

In 1 of the ws (out of 15) in my Excel workbook; B2:B200 generates +ve integers including 0 i.e. 0,1,2,3,4,5,... PLUS some cells in B2:B200 are blank (since no values/formulas being present there).

The corresponding C2:C200 & D2:D200 contains formulas & generates numerical values.
Output required: Formulas in corresponding C2:C200 SHOULD GET REMOVED IMMEDIATELY for corresponding B2:B200 generating 3.

Ex:
B2=1(thr' formula) C2=D2(formula) D2=200.32 (thr' formula)
B2
.
.
Now if:
B2=3 THE FORMULA IN C2 SHOULD GET REMOVED IMMEDIATELY

How to accomplish?
Thanks in advance.

 

[MARK858]

On my phone so can't check anything but afraid I can't see how you can have a 3 in column B and the corresponding cell in column C not be a value when the code is run on the activesheet and so can't be of any help.

 

[hsandeep]

Just now checked the vba...The Left hand side shows "(Declarations)" & the right hand side "test". Should it be made to be kept at "(Declarations)" for the code to work 'automatically'?

 

[MARK858]

The code won't work automatically, it is not worksheet event code you put it in a standard module and have to run it as a standard sub.
What you haven't stated is why you need it converted to formulas as it is still a puzzle to me.

 

[hsandeep]

The code won't work automatically, it is not worksheet event code you put it in a standard module and have to run it as a standard sub. What should I do to make it trigger automatically for any 3 found in column B. Could not understand what is "have to run it as a standard sub". I am using it in a ws of an Excel workbook which has more than 20 ws & out of which 1 ws has real time feed.
What you haven't stated is why you need it converted to formulas as it is still a puzzle to me.

I'll explain you...but it will require some more terms, so please allow me a day or 2.

 

[hsandeep]

I don't know "standard module". I have pasted the code by right clicking the ws's tab name & then 'view code' | after which a window opens.
I am using Excel 2007.
Meanwhile please help me with query in #24

 

[hsandeep]

What you haven't stated is why you need it converted to formulas as it is still a puzzle to me.
I'll try to elaborate:

D is constantly getting updated in values say 220.32, 220.38, 222.46, .......using RTD
value 3 occurs in column B ONLY when column D > a defined value else column B generates other than 3 (null, 1,2,4,). I am doing some calculation using D ONLY WHEN B=3. For this I use column C as =D & want C to become formula free as soon as B=3. This calculation requires value D AT THE MOMENT WHEN B=3. So as soon as B=3, calculation is done in some other ws to get a value. But if B becomes 2, than I don't need the 'calculated' values & would wait for B becoming 3 again to START WITH A FRESH CALCULATION. So I needed the FORMULA BACK in C & thereafter to make C again FORMULA FREE if B=3 & so on.....

 

[MARK858]

hsandeep, first of all I only respond with code during spare time I have (which for the next couple of days is a very small amount of time) and virtually never when I am at work.

Secondly to achieve what you want you need to supply a fair bit more information.

1) how is the 3 in column B being entered? manually, by formula, by macro, by import or by some other method?
2) how is the data being up updated i.e. how is the information being imported into the sheet?
3) how often is the data being updated?
4) how many rows and columns are we dealing with?
5) Why can't you pull the information from Column D with a macro as a value into your calculation at the same time as Column B changes to 3 (which brings it back to question number 1)?
6) What calculation is the value being used for? is it a formula calculation or a macro calculation (whichever it is, post it)?
7) what does the end data look like (see question 8)?
8) can you supply usable screenshots of your data, both sheets if the calculations are being done on another sheet (sanitize any sensitive data). See my signature block for ways to do this

Being honest I am a bit concerned about giving you code you don't understand especially if we end up having to use something like application.ontime when you don't understand what the code is doing.

I will have a look at this once you have answered the questions above but like I said it probably won't be for a couple of days but someone might step in with an idea before then.

 

[hsandeep]

Secondly to achieve what you want you need to supply a fair bit more information. Note: Your code is required in ws with tab named "D3" & calculation done in ws with tab named "E3"

1) how is the 3 in column B being entered? manually, by formula, by macro, by import or by some other method? By formula screenshot attached
2) how is the data being up updated i.e. how is the information being imported into the sheet? from another ws by simple Excel function "=" screenshot attached
3) how often is the data being updated? The data gets updated every second/milli-seconds thr' real time feed in another ws tab named "7". There is no macros or real time data 'directly' feeded in the current ws which requires your code & which is tab named "D3"
4) how many rows and columns are we dealing with? screen shot attached for ws D3
5) Why can't you pull the information from Column D with a macro as a value into your calculation at the same time as Column B changes to 3 (which brings it back to question number 1)? My 'Result' see.."E3"....'continuous' value of 3 in the cell of column C of "D3" is required. screen shot attached. I want my formula to run on E3 to give me the desired result.
6) What calculation is the value being used for? is it a formula calculation or a macro calculation (whichever it is, post it)? formula calculation shown in "E3". screen shot attached
7) what does the end data look like (see question 8)? End data gives a 'Result' generated thr' formula in "E3" in column C screen shot attached
8) can you supply usable screenshots of your data, both sheets if the calculations are being done on another sheet (sanitize any sensitive data). See my signature block for ways to do this screen shots are attached.

Being honest I am a bit concerned about giving you code you don't understand especially if we end up having to use something like application.ontime when you don't understand what the code is doing.

Lastly MARK858 please understand I also know every time & energy spent should be utilized & your code (if given) would be used for meeting a desired & pre-defined answer which would help me ALWAYS in days ahead. You may ask further queries if required. I would appreciate if the help is provided..of course you may do it as per your time-convenience. Regards & the Screen shots are being attached in next actions.

I will have a look at this once you have answered the questions above but like I said it probably won't be for a couple of days but someone might step in with an idea before then.[/QUOTE]

 

[hsandeep]

MARK858, Sorry for some delay...got used to Excel Jeanie courtesy (Peter SS)......
Screen shots attached:
SS1
D3 In this B3=3, C3 to be made FORMULA FREE & again the same formula '=D3' to be re-pushed if B3 is not equal to 3. (Your code needed for this purpose)

	A	B	C	D	E	F
1						Time
2	Ktk	2	125.15	125.15	1752716	9/18/2014 19:02
3	YES	3	591.23	590.30	2868279	9/18/2014 19:02
4	UBI	4	218.67	218.67	4081693	9/18/2014 19:02
5	BOI	4	292.95	292.95	2332353	9/18/2014 19:02
6	ICICI	1	1553.76	1553.76	1219288	9/18/2014 19:02
7	AXIS	2	402.24	402.24	3458236	9/18/2014 19:02
8	SBI	4	2608.38	2608.38	2012120	9/18/2014 19:02
9	Indus	1	624.80	624.80	870506	9/18/2014 19:02
10	BOB	4	948.38	948.38	1048066	9/18/2014 19:02
11	PNB	4	987.84	987.84	949759	9/18/2014 19:02
12	Canara	2	398.31	398.31	2141765	9/18/2014 19:02
13	Rcap	2	527.91	527.91	2795009	9/18/2014 19:02
14	Rcom	2	104.62	104.62	5946117	9/18/2014 19:02
15	Tatamo	1	517.04	517.04	7051279	9/18/2014 19:02
16	IBR	0	67.63	67.63	10467233	9/18/2014 19:02
17	DLF	1	173.88	173.88	5788175	9/18/2014 19:02
18	TVS	4	218.77	218.77	4759894	9/18/2014 19:02
19	BHEL	1	225.79	225.79	5472760	9/18/2014 19:02
20	JSPL	2	218.91	218.91	4499908	9/18/2014 19:02
21	HDIL	0	90.82	90.82	25952319	9/18/2014 19:02
22	Fed	4	125.03	125.03	2569815	9/18/2014 19:02
23	Ran	4	603.39	603.39	802286	9/18/2014 19:02
24	AdEnt	1	505.21	505.21	2656415	9/18/2014 19:02
25	AdPo	0	289.33	289.33	3210047	9/18/2014 19:02
26	IDFC	4	147.04	147.04	6636775	9/18/2014 19:02
27	CG	4	218.60	218.60	2490536	9/18/2014 19:02
28	Infy	1	3695.90	3695.90	1138097	9/18/2014 19:02
29	TCS	1	2627.38	2627.38	741539	9/18/2014 19:02
30	MS	2	425.82	425.82	2397198	9/18/2014 19:02
31	SKS	2	315.33	315.33	1543035	9/18/2014 19:02
32	Tatastl	4	505.30	505.30	4870457	9/18/2014 19:02
33	Hindal	2	163.16	163.16	4836865	9/18/2014 19:02
34	UPL	2	356.96	356.96	2330658	9/18/2014 19:02
35	SSLT	2	285.02	285.02	4220101	9/18/2014 19:02
36	Coal	2	345.07	345.07	2647582	9/18/2014 19:02
37	REC	2	266.46	266.46	2353054	9/18/2014 19:02
38	PFC	2	246.70	246.70	4084793	9/18/2014 19:02
39	N	1	8082.27	8082.27	15907850	9/18/2014 19:02
40	BN	4	16076.80	16076.80	3197900	9/18/2014 19:02
41	Ori	4	288.50	288.50	1544928	9/18/2014 19:02
42	ONGC	2	413.22	413.22	4280174	9/18/2014 19:02
43	Ktk	2	1752716
44	YES	3	3127014
45	UBI	4	4081693
46	BOI	4	2332353
47	ICICI	1	1219288
48	AXIS	2	3458236
49	SBI	4	2012120
50	Indus	1	870506
51	BOB	4	1048066
52	PNB	4	949759
53	Canara	2	2141765
54	Rcap	2	2795009
55	Rcom	2	5946117
56	Tatamo	1	7051279
57	IBR	0	10467233
58	DLF	1	5788175
59	TVS	4	4759894
60	BHEL	1	5472760
61	JSPL	2	4499908
62	HDIL	0	25952319
63	Fed	4	2569815
64	Ran	4	802286
65	AdEnt	1	2656415
66	AdPo	0	3210047
67	IDFC	4	6636775
68	CG	4	2490536
69	Infy	1	1138097
70	TCS	1	741539
71	MS	2	2397198
72	SKS	2	1543035
73	Tatastl	4	4870457
74	Hindal	2	4836865
75	UPL	2	2330658
76	SSLT	2	4220101
77	Coal	2	2647582
78	REC	2	2353054
79	PFC	2	4084793
80	N	1	15907850
81	BN	4	3197900
82	Ori	4	1544928
83	ONGC	2	4280174

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 62px"><col style="WIDTH: 115px"><col style="WIDTH: 115px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 110px"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B2 =V!X12 C2 =D2 D2 ='7'!L22 E2 ='7'!M22 F2 =NOW() B3 =V!X16 D3 ='7'!L16 E3 ='7'!M16 F3 =NOW() B4 =V!X15 C4 =D4 D4 ='7'!L18 E4 ='7'!M18 F4 =NOW() B5 =V!X17 C5 =D5 D5 ='7'!L14 E5 ='7'!M14 F5 =NOW() B6 =V!X9 C6 =D6 D6 ='7'!L26 E6 ='7'!M26 F6 =NOW() B7 =V!X10 C7 =D7 D7 ='7'!L28 E7 ='7'!M28 F7 =NOW() B8 =V!X11 C8 =D8 D8 ='7'!L30 E8 ='7'!M30 F8 =NOW() B9 =V!X13 C9 =D9 D9 ='7'!L20 E9 ='7'!M20 F9 =NOW() B10 =V!X14 C10 =D10 D10 ='7'!L24 E10 ='7'!M24 F10 =NOW() B11 =V!X61 C11 =D11 D11 ='7'!L58 E11 ='7'!M58 F11 =NOW() B12 =V!X79 C12 =D12 D12 ='7'!L75 E12 ='7'!M75 F12 =NOW() B13 =V!X41 C13 =D13 D13 ='7'!L46 E13 ='7'!M46 F13 =NOW() B14 =V!X42 C14 =D14 D14 ='7'!L48 E14 ='7'!M48 F14 =NOW() B15 =V!X45 C15 =D15 D15 ='7'!L50 E15 ='7'!M50 F15 =NOW() B16 =V!X43 C16 =D16 D16 ='7'!L54 E16 ='7'!M54 F16 =NOW() B17 =V!X44 C17 =D17 D17 ='7'!L52 E17 ='7'!M52 F17 =NOW() B18 =V!X67 C18 =D18 D18 ='7'!L64 E18 ='7'!M64 F18 =NOW() B19 =V!X68 C19 =D19 D19 ='7'!L66 E19 ='7'!M66 F19 =NOW() B20 =V!X73 C20 =D20 D20 ='7'!L69 E20 ='7'!M69 F20 =NOW() B21 =V!X76 C21 =D21 D21 ='7'!L72 E21 ='7'!M72 F21 =NOW() B22 =V!X85 C22 =D22 D22 ='7'!L81 E22 ='7'!M81 F22 =NOW() B23 =V!X88 C23 =D23 D23 ='7'!L84 E23 ='7'!M84 F23 =NOW() B24 =V!X91 C24 =D24 D24 ='7'!L87 E24 ='7'!M87 F24 =NOW() B25 =V!X94 C25 =D25 D25 ='7'!L90 E25 ='7'!M90 F25 =NOW() B26 =V!X112 C26 =D26 D26 ='7'!L108 E26 ='7'!M108 F26 =NOW() B27 =V!X97 C27 =D27 D27 ='7'!L93 E27 ='7'!M93 F27 =NOW() B28 =V!X100 C28 =D28 D28 ='7'!L96 E28 ='7'!M96 F28 =NOW() B29 =V!X103 C29 =D29 D29 ='7'!L99 E29 ='7'!M99 F29 =NOW() B30 =V!X106 C30 =D30 D30 ='7'!L102 E30 ='7'!M102 F30 =NOW() B31 =V!X109 C31 =D31 D31 ='7'!L105 E31 ='7'!M105 F31 =NOW() B32 =V!X38 C32 =D32 D32 ='7'!L42 E32 ='7'!M42 F32 =NOW() B33 =V!X39 C33 =D33 D33 ='7'!L44 E33 ='7'!M44 F33 =NOW() B34 =V!X115 C34 =D34 D34 ='7'!L111 E34 ='7'!M111 F34 =NOW() B35 =V!X119 C35 =D35 D35 ='7'!L119 E35 ='7'!M119 F35 =NOW() B36 =V!X118 C36 =D36 D36 ='7'!L117 E36 ='7'!M117 F36 =NOW() B37 =V!X120 C37 =D37 D37 ='7'!L121 E37 ='7'!M121 F37 =NOW() B38 =V!X121 C38 =D38 D38 ='7'!L123 E38 ='7'!M123 F38 =NOW() B39 =V!X7 C39 =D39 D39 ='7'!L2 E39 ='7'!M2 F39 =NOW() B40 =V!X8 C40 =D40 D40 ='7'!L3 E40 ='7'!M3 F40 =NOW() B41 =V!X130 C41 =D41 D41 ='7'!L126 E41 ='7'!M126 F41 =NOW() B42 =V!X133 C42 =D42 D42 ='7'!L129 E42 ='7'!M129 F42 =NOW() B43 =B2 C43 =E2 B45 =B4 C45 =E4 B46 =B5 C46 =E5 B47 =B6 C47 =E6 B48 =B7 C48 =E7 B49 =B8 C49 =E8 B50 =B9 C50 =E9 B51 =B10 C51 =E10 B52 =B11 C52 =E11 B53 =B12 C53 =E12 B54 =B13 C54 =E13 B55 =B14 C55 =E14 B56 =B15 C56 =E15 B57 =B16 C57 =E16 B58 =B17 C58 =E17 B59 =B18 C59 =E18 B60 =B19 C60 =E19 B61 =B20 C61 =E20 B62 =B21 C62 =E21 B63 =B22 C63 =E22 B64 =B23 C64 =E23 B65 =B24 C65 =E24 B66 =B25 C66 =E25 B67 =B26 C67 =E26 B68 =B27 C68 =E27 B69 =B28 C69 =E28 B70 =B29 C70 =E29 B71 =B30 C71 =E30 B72 =B31 C72 =E31 B73 =B32 C73 =E32 B74 =B33 C74 =E33 B75 =B34 C75 =E34 B76 =B35 C76 =E35 B77 =B36 C77 =E36 B78 =B37 C78 =E37 B79 =B38 C79 =E38 B80 =B39 C80 =E39 B81 =B40 C81 =E40 B82 =B41 C82 =E41 B83 =B42 C83 =E42 <tbody> </tbody>

<tbody>
</tbody>

SS2:
E3 The ws being used by me for calculation purpose.

	B	C	D	E	F	G	H	I	J
1		Result	CM LTP	Avg_NSE	Vol	TO	Avg_NSE	Vol	TO
2	Ktk	0.00	126.35	125.15	1752716	219352407	125.15	1752716	219352407
3	YES	601.54	594.80	590.30	2868279	1693145094	591.23	3127014	1848784487
4	UBI	0.00	221.95	218.67	4081693	892543808	218.67	4081693	892543808
5	BOI	0.00	296.20	292.95	2332353	683262811	292.95	2332353	683262811
6	ICICI	0.00	1565.00	1553.76	1219288	1894480923	1553.76	1219288	1894480923
7	AXIS	0.00	405.30	402.24	3458236	1391040849	402.24	3458236	1391040849
8	SBI	0.00	2620.40	2608.38	2012120	5248373566	2608.38	2012120	5248373566
9	Indus	0.00	629.55	624.80	870506	543892149	624.80	870506	543892149
10	BOB	0.00	957.25	948.38	1048066	993964833	948.38	1048066	993964833
11	PNB	0.00	990.95	987.84	949759	938209931	987.84	949759	938209931
12	Canara	0.00	402.15	398.31	2141765	853086417	398.31	2141765	853086417
13	Rcap	0.00	530.75	527.91	2795009	1475513201	527.91	2795009	1475513201
14	Rcom	0.00	105.20	104.62	5946117	622082761	104.62	5946117	622082761
15	Tatamo	0.00	525.40	517.04	7051279	3645793294	517.04	7051279	3645793294
16	IBR	0.00	67.60	67.63	10467233	707898968	67.63	10467233	707898968
17	DLF	0.00	175.30	173.88	5788175	1006447869	173.88	5788175	1006447869
18	TVS	0.00	221.70	218.77	4759894	1041322010	218.77	4759894	1041322010
19	BHEL	0.00	228.60	225.79	5472760	1235694480	225.79	5472760	1235694480
20	JSPL	0.00	220.95	218.91	4499908	985074860	218.91	4499908	985074860
21	HDIL	0.00	93.50	90.82	25952319	2356989612	90.82	25952319	2356989612
22	Fed	0.00	125.35	125.03	2569815	321303969	125.03	2569815	321303969
23	Ran	0.00	606.55	603.39	802286	484091350	603.39	802286	484091350
24	AdEnt	0.00	509.40	505.21	2656415	1342047422	505.21	2656415	1342047422
25	AdPo	0.00	294.60	289.33	3210047	928762899	289.33	3210047	928762899
26	IDFC	0.00	147.40	147.04	6636775	975871396	147.04	6636775	975871396
27	CG	0.00	218.80	218.60	2490536	544431170	218.60	2490536	544431170
28	Infy	0.00	3697.00	3695.90	1138097	4206292702	3695.90	1138097	4206292702
29	TCS	0.00	2642.30	2627.38	741539	1948304738	2627.38	741539	1948304738
30	MS	0.00	422.70	425.82	2397198	1020774852	425.82	2397198	1020774852
31	SKS	0.00	315.15	315.33	1543035	486565227	315.33	1543035	486565227
32	Tatastl	0.00	505.45	505.30	4870457	2461041922	505.30	4870457	2461041922
33	Hindal	0.00	163.00	163.16	4836865	789182893	163.16	4836865	789182893
34	UPL	0.00	359.40	356.96	2330658	831951680	356.96	2330658	831951680
35	SSLT	0.00	284.80	285.02	4220101	1202813187	285.02	4220101	1202813187
36	Coal	0.00	344.90	345.07	2647582	913601121	345.07	2647582	913601121
37	REC	0.00	269.40	266.46	2353054	626994769	266.46	2353054	626994769
38	PFC	0.00	248.30	246.70	4084793	1007718433	246.70	4084793	1007718433
39	N	0.00	8132.75	8082.27	15907850	##########	8082.27	15907850	###########
40	BN	0.00	16203.00	16076.80	3197900	##########	16076.80	3197900	51411998720
41	Ori	0.00	292.20	288.50	1544928	445711728	288.50	1544928	445711728
42	ONGC	0.00	413.90	413.22	4280174	1768653500	413.22	4280174	1768653500

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 83px"><col style="WIDTH: 114px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 81px"><col style="WIDTH: 81px"><col style="WIDTH: 81px"><col style="WIDTH: 81px"><col style="WIDTH: 84px"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula C2 =IF((F2-I2)=0,0,((G2-J2)/(F2-I2))) D2 ='7'!H22 E2 ='7'!L22 F2 ='7'!M22 G2 =F2*E2 H2 ='D3'!C2 I2 ='D3'!C43 J2 =I2*H2 C3 =IF((F3-I3)=0,0,((G3-J3)/(F3-I3))) D3 ='7'!H16 E3 ='7'!L16 F3 ='7'!M16 G3 =F3*E3 H3 ='D3'!C3 I3 ='D3'!C44 J3 =I3*H3 C4 =IF((F4-I4)=0,0,((G4-J4)/(F4-I4))) D4 ='7'!H18 E4 ='7'!L18 F4 ='7'!M18 G4 =F4*E4 H4 ='D3'!C4 I4 ='D3'!C45 J4 =I4*H4 C5 =IF((F5-I5)=0,0,((G5-J5)/(F5-I5))) D5 ='7'!H14 E5 ='7'!L14 F5 ='7'!M14 G5 =F5*E5 H5 ='D3'!C5 I5 ='D3'!C46 J5 =I5*H5 C6 =IF((F6-I6)=0,0,((G6-J6)/(F6-I6))) D6 ='7'!H26 E6 ='7'!L26 F6 ='7'!M26 G6 =F6*E6 H6 ='D3'!C6 I6 ='D3'!C47 J6 =I6*H6 C7 =IF((F7-I7)=0,0,((G7-J7)/(F7-I7))) D7 ='7'!H28 E7 ='7'!L28 F7 ='7'!M28 G7 =F7*E7 H7 ='D3'!C7 I7 ='D3'!C48 J7 =I7*H7 C8 =IF((F8-I8)=0,0,((G8-J8)/(F8-I8))) D8 ='7'!H30 E8 ='7'!L30 F8 ='7'!M30 G8 =F8*E8 H8 ='D3'!C8 I8 ='D3'!C49 J8 =I8*H8 C9 =IF((F9-I9)=0,0,((G9-J9)/(F9-I9))) D9 ='7'!H20 E9 ='7'!L20 F9 ='7'!M20 G9 =F9*E9 H9 ='D3'!C9 I9 ='D3'!C50 J9 =I9*H9 C10 =IF((F10-I10)=0,0,((G10-J10)/(F10-I10))) D10 ='7'!H24 E10 ='7'!L24 F10 ='7'!M24 G10 =F10*E10 H10 ='D3'!C10 I10 ='D3'!C51 J10 =I10*H10 C11 =IF((F11-I11)=0,0,((G11-J11)/(F11-I11))) D11 ='7'!H58 E11 ='7'!L58 F11 ='7'!M58 G11 =F11*E11 H11 ='D3'!C11 I11 ='D3'!C52 J11 =I11*H11 C12 =IF((F12-I12)=0,0,((G12-J12)/(F12-I12))) D12 ='7'!H75 E12 ='7'!L75 F12 ='7'!M75 G12 =F12*E12 H12 ='D3'!C12 I12 ='D3'!C53 J12 =I12*H12 C13 =IF((F13-I13)=0,0,((G13-J13)/(F13-I13))) D13 ='7'!H46 E13 ='7'!L46 F13 ='7'!M46 G13 =F13*E13 H13 ='D3'!C13 I13 ='D3'!C54 J13 =I13*H13 C14 =IF((F14-I14)=0,0,((G14-J14)/(F14-I14))) D14 ='7'!H48 E14 ='7'!L48 F14 ='7'!M48 G14 =F14*E14 H14 ='D3'!C14 I14 ='D3'!C55 J14 =I14*H14 C15 =IF((F15-I15)=0,0,((G15-J15)/(F15-I15))) D15 ='7'!H50 E15 ='7'!L50 F15 ='7'!M50 G15 =F15*E15 H15 ='D3'!C15 I15 ='D3'!C56 J15 =I15*H15 C16 =IF((F16-I16)=0,0,((G16-J16)/(F16-I16))) D16 ='7'!H54 E16 ='7'!L54 F16 ='7'!M54 G16 =F16*E16 H16 ='D3'!C16 I16 ='D3'!C57 J16 =I16*H16 C17 =IF((F17-I17)=0,0,((G17-J17)/(F17-I17))) D17 ='7'!H52 E17 ='7'!L52 F17 ='7'!M52 G17 =F17*E17 H17 ='D3'!C17 I17 ='D3'!C58 J17 =I17*H17 C18 =IF((F18-I18)=0,0,((G18-J18)/(F18-I18))) D18 ='7'!H64 E18 ='7'!L64 F18 ='7'!M64 G18 =F18*E18 H18 ='D3'!C18 I18 ='D3'!C59 J18 =I18*H18 C19 =IF((F19-I19)=0,0,((G19-J19)/(F19-I19))) D19 ='7'!H66 E19 ='7'!L66 F19 ='7'!M66 G19 =F19*E19 H19 ='D3'!C19 I19 ='D3'!C60 J19 =I19*H19 C20 =IF((F20-I20)=0,0,((G20-J20)/(F20-I20))) D20 ='7'!H69 E20 ='7'!L69 F20 ='7'!M69 G20 =F20*E20 H20 ='D3'!C20 I20 ='D3'!C61 J20 =I20*H20 C21 =IF((F21-I21)=0,0,((G21-J21)/(F21-I21))) D21 ='7'!H72 E21 ='7'!L72 F21 ='7'!M72 G21 =F21*E21 H21 ='D3'!C21 I21 ='D3'!C62 J21 =I21*H21 C22 =IF((F22-I22)=0,0,((G22-J22)/(F22-I22))) D22 ='7'!H81 E22 ='7'!L81 F22 ='7'!M81 G22 =F22*E22 H22 ='D3'!C22 I22 ='D3'!C63 J22 =I22*H22 C23 =IF((F23-I23)=0,0,((G23-J23)/(F23-I23))) D23 ='7'!H84 E23 ='7'!L84 F23 ='7'!M84 G23 =F23*E23 H23 ='D3'!C23 I23 ='D3'!C64 J23 =I23*H23 C24 =IF((F24-I24)=0,0,((G24-J24)/(F24-I24))) D24 ='7'!H87 E24 ='7'!L87 F24 ='7'!M87 G24 =F24*E24 H24 ='D3'!C24 I24 ='D3'!C65 J24 =I24*H24 C25 =IF((F25-I25)=0,0,((G25-J25)/(F25-I25))) D25 ='7'!H90 E25 ='7'!L90 F25 ='7'!M90 G25 =F25*E25 H25 ='D3'!C25 I25 ='D3'!C66 J25 =I25*H25 C26 =IF((F26-I26)=0,0,((G26-J26)/(F26-I26))) D26 ='7'!H108 E26 ='7'!L108 F26 ='7'!M108 G26 =F26*E26 H26 ='D3'!C26 I26 ='D3'!C67 J26 =I26*H26 C27 =IF((F27-I27)=0,0,((G27-J27)/(F27-I27))) D27 ='7'!H93 E27 ='7'!L93 F27 ='7'!M93 G27 =F27*E27 H27 ='D3'!C27 I27 ='D3'!C68 J27 =I27*H27 C28 =IF((F28-I28)=0,0,((G28-J28)/(F28-I28))) D28 ='7'!H96 E28 ='7'!L96 F28 ='7'!M96 G28 =F28*E28 H28 ='D3'!C28 I28 ='D3'!C69 J28 =I28*H28 C29 =IF((F29-I29)=0,0,((G29-J29)/(F29-I29))) D29 ='7'!H99 E29 ='7'!L99 F29 ='7'!M99 G29 =F29*E29 H29 ='D3'!C29 I29 ='D3'!C70 J29 =I29*H29 C30 =IF((F30-I30)=0,0,((G30-J30)/(F30-I30))) D30 ='7'!H102 E30 ='7'!L102 F30 ='7'!M102 G30 =F30*E30 H30 ='D3'!C30 I30 ='D3'!C71 J30 =I30*H30 C31 =IF((F31-I31)=0,0,((G31-J31)/(F31-I31))) D31 ='7'!H105 E31 ='7'!L105 F31 ='7'!M105 G31 =F31*E31 H31 ='D3'!C31 I31 ='D3'!C72 J31 =I31*H31 C32 =IF((F32-I32)=0,0,((G32-J32)/(F32-I32))) D32 ='7'!H42 E32 ='7'!L42 F32 ='7'!M42 G32 =F32*E32 H32 ='D3'!C32 I32 ='D3'!C73 J32 =I32*H32 C33 =IF((F33-I33)=0,0,((G33-J33)/(F33-I33))) D33 ='7'!H44 E33 ='7'!L44 F33 ='7'!M44 G33 =F33*E33 H33 ='D3'!C33 I33 ='D3'!C74 J33 =I33*H33 C34 =IF((F34-I34)=0,0,((G34-J34)/(F34-I34))) D34 ='7'!H111 E34 ='7'!L111 F34 ='7'!M111 G34 =F34*E34 H34 ='D3'!C34 I34 ='D3'!C75 J34 =I34*H34 C35 =IF((F35-I35)=0,0,((G35-J35)/(F35-I35))) D35 ='7'!H119 E35 ='7'!L119 F35 ='7'!M119 G35 =F35*E35 H35 ='D3'!C35 I35 ='D3'!C76 J35 =I35*H35 C36 =IF((F36-I36)=0,0,((G36-J36)/(F36-I36))) D36 ='7'!H117 E36 ='7'!L117 F36 ='7'!M117 G36 =F36*E36 H36 ='D3'!C36 I36 ='D3'!C77 J36 =I36*H36 C37 =IF((F37-I37)=0,0,((G37-J37)/(F37-I37))) D37 ='7'!H121 E37 ='7'!L121 F37 ='7'!M121 G37 =F37*E37 H37 ='D3'!C37 I37 ='D3'!C78 J37 =I37*H37 C38 =IF((F38-I38)=0,0,((G38-J38)/(F38-I38))) D38 ='7'!H123 E38 ='7'!L123 F38 ='7'!M123 G38 =F38*E38 H38 ='D3'!C38 I38 ='D3'!C79 J38 =I38*H38 C39 =IF((F39-I39)=0,0,((G39-J39)/(F39-I39))) D39 ='7'!H2 E39 ='7'!L2 F39 ='7'!M2 G39 =F39*E39 H39 ='D3'!C39 I39 ='D3'!C80 J39 =I39*H39 C40 =IF((F40-I40)=0,0,((G40-J40)/(F40-I40))) D40 ='7'!H3 E40 ='7'!L3 F40 ='7'!M3 G40 =F40*E40 H40 ='D3'!C40 I40 ='D3'!C81 J40 =I40*H40 C41 =IF((F41-I41)=0,0,((G41-J41)/(F41-I41))) D41 ='7'!H126 E41 ='7'!L126 F41 ='7'!M126 G41 =F41*E41 H41 ='D3'!C41 I41 ='D3'!C82 J41 =I41*H41 C42 =IF((F42-I42)=0,0,((G42-J42)/(F42-I42))) D42 ='7'!H129 E42 ='7'!L129 F42 ='7'!M129 G42 =F42*E42 H42 ='D3'!C42 I42 ='D3'!C83 J42 =I42*H42 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML

Excel tables to the web >> Excel

 

[MARK858]

Hi hansdeep I've had a quick look at this and the obvious issue is

1) how is the 3 in column B being entered? manually, by formula, by macro, by import or by some other method? By formula screenshot attached

I am sure you have seen in other posts that you can't trigger a macro by a change in a formula result. You can use the Calculate event, application ontime etc. but if your sheet is being updated every "second/milli-seconds" (actually it is either one or the other and it is a big difference) then either method could kill your sheet performance.

This is why I am interested in how the data is being pulled in.

There is no macros or real time data 'directly' feeded in the current ws which requires your code & which is tab named "D3

It doesn't matter what sheet the data is being pulled/fed into, what matters is the method being used and whether we can use the feed to trigger the macro (if it is being pulled in by a macro it will make life easier ).

Basically I need you have a think about how you want the macro to be triggered