How to sort in excel i have folowing data and i want highlighted columns can be sorted?

[trustmeasfrnd]

i want to sort corresponding to column O and T using macro button for ascending/descending values. the problem is my column A has Plate mark only for one case till all load cases changes. how to add plate mark in all rows(may be needed another excel Macro button to do that)
Also note that row can be more than shown here.
one more help needed can i reduce using formula for columns K L M N O and P Q R S T and rows as same formula is repeted?

Thanks in Advance.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
	Shear	Membrane	Bending Moment
Plate	L/C	SQX (local) N/mm2	SQY (local) N/mm2	SX (local) N/mm2	SY (local) N/mm2	SXY (local) N/mm2	Mx1	My1	Mx2	My2	Mxd	Mx1	My1	Mx2	My2	Mxd
301 1.5DL1 + 1.5LL
302 1.2DL1 + 1.2LL + 1.2WLX (W-E)
303 1.2DL1 + 1.2LL - 1.2WLX (W-E)
304 1.2DL1 + 1.2LL + 1.2WLZ (N-S)
305 1.2DL1 + 1.2LL - 1.2WLZ (N-S)
310 1.5DL1 + 1.5WLX (W-E)
301 1.5DL1 + 1.5LL
302 1.2DL1 + 1.2LL + 1.2WLX (W-E)
303 1.2DL1 + 1.2LL - 1.2WLX (W-E)
304 1.2DL1 + 1.2LL + 1.2WLZ (N-S)
305 1.2DL1 + 1.2LL - 1.2WLZ (N-S)
301 1.5DL1 + 1.5LL
302 1.2DL1 + 1.2LL + 1.2WLX (W-E)
303 1.2DL1 + 1.2LL - 1.2WLX (W-E)
304 1.2DL1 + 1.2LL + 1.2WLZ (N-S)
305 1.2DL1 + 1.2LL - 1.2WLZ (N-S)
301 1.5DL1 + 1.5LL
302 1.2DL1 + 1.2LL + 1.2WLX (W-E)
303 1.2DL1 + 1.2LL - 1.2WLX (W-E)
304 1.2DL1 + 1.2LL + 1.2WLZ (N-S)
305 1.2DL1 + 1.2LL - 1.2WLZ (N-S)

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Mxd For TOP REINFORCEMENT[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Mxd For BOTTOM REINFORCEMENT[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]Mx kNm/m[/TD]
[TD="align: center"]My kNm/m[/TD]
[TD="align: center"]Mxy kNm/m[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1422[/TD]

[TD="align: right"]0.016[/TD]
[TD="align: right"]0.028[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.183[/TD]
[TD="align: right"]6.739[/TD]
[TD="align: right"]0.039[/TD]
[TD="align: right"]-0.144[/TD]
[TD="align: right"]6.778[/TD]
[TD="align: right"]-0.18277[/TD]
[TD="align: right"]6.747311[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]-0.222[/TD]
[TD="align: right"]6.7[/TD]
[TD="align: right"]-0.18323[/TD]
[TD="align: right"]6.730689[/TD]
[TD="align: right"]-0.183[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.024[/TD]
[TD="align: right"]0.033[/TD]
[TD="align: right"]-0.001[/TD]
[TD="align: right"]-0.001[/TD]
[TD="align: right"]-0.001[/TD]
[TD="align: right"]-1.071[/TD]
[TD="align: right"]7.951[/TD]
[TD="align: right"]0.552[/TD]
[TD="align: right"]-0.519[/TD]
[TD="align: right"]8.503[/TD]
[TD="align: right"]-1.03268[/TD]
[TD="align: right"]8.235504[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]-1.623[/TD]
[TD="align: right"]7.399[/TD]
[TD="align: right"]-1.10932[/TD]
[TD="align: right"]7.666496[/TD]
[TD="align: right"]-1.109[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.001[/TD]
[TD="align: right"]0.011[/TD]
[TD="align: right"]0.001[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.799[/TD]
[TD="align: right"]2.813[/TD]
[TD="align: right"]-0.489[/TD]
[TD="align: right"]1.288[/TD]
[TD="align: right"]3.302[/TD]
[TD="align: right"]0.884006[/TD]
[TD="align: right"]3.112275[/TD]
[TD="align: right"]1.288[/TD]
[TD="align: right"]0.31[/TD]
[TD="align: right"]2.324[/TD]
[TD="align: right"]0.713994[/TD]
[TD="align: right"]2.513725[/TD]
[TD="align: right"]0.000[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.011[/TD]
[TD="align: right"]0.02[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.171[/TD]
[TD="align: right"]4.031[/TD]
[TD="align: right"]-0.705[/TD]
[TD="align: right"]0.534[/TD]
[TD="align: right"]4.736[/TD]
[TD="align: right"]-0.0477[/TD]
[TD="align: right"]6.937579[/TD]
[TD="align: right"]0.534[/TD]
[TD="align: right"]-0.876[/TD]
[TD="align: right"]3.326[/TD]
[TD="align: right"]-0.2943[/TD]
[TD="align: right"]1.124421[/TD]
[TD="align: right"]-0.294[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.014[/TD]
[TD="align: right"]0.024[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.07[/TD]
[TD="align: right"]6.806[/TD]
[TD="align: right"]0.811[/TD]
[TD="align: right"]0.741[/TD]
[TD="align: right"]7.617[/TD]
[TD="align: right"]0.026638[/TD]
[TD="align: right"]16.20201[/TD]
[TD="align: right"]0.741[/TD]
[TD="align: right"]-0.881[/TD]
[TD="align: right"]5.995[/TD]
[TD="align: right"]-0.16664[/TD]
[TD="align: right"]-2.59001[/TD]
[TD="align: right"]-0.167[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.029[/TD]
[TD="align: right"]0.04[/TD]
[TD="align: right"]-0.001[/TD]
[TD="align: right"]-0.001[/TD]
[TD="align: right"]-0.001[/TD]
[TD="align: right"]-1.266[/TD]
[TD="align: right"]9.638[/TD]
[TD="align: right"]0.595[/TD]
[TD="align: right"]-0.671[/TD]
[TD="align: right"]10.233[/TD]
[TD="align: right"]-1.22927[/TD]
[TD="align: right"]9.917641[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]-1.861[/TD]
[TD="align: right"]9.043[/TD]
[TD="align: right"]-1.30273[/TD]
[TD="align: right"]9.358359[/TD]
[TD="align: right"]-1.303[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1419[/TD]

[TD="align: right"]0.03[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]0.002[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.002[/TD]
[TD="align: right"]1.289[/TD]
[TD="align: right"]76.493[/TD]
[TD="align: right"]1.941[/TD]
[TD="align: right"]3.23[/TD]
[TD="align: right"]78.434[/TD]
[TD="align: right"]1.338253[/TD]
[TD="align: right"]79.41579[/TD]
[TD="align: right"]3.230[/TD]
[TD="align: right"]-0.652[/TD]
[TD="align: right"]74.552[/TD]
[TD="align: right"]1.239747[/TD]
[TD="align: right"]73.57021[/TD]
[TD="align: right"]1.240[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.037[/TD]
[TD="align: right"]0.047[/TD]
[TD="align: right"]0.001[/TD]
[TD="align: right"]-0.002[/TD]
[TD="align: right"]0.002[/TD]
[TD="align: right"]-0.214[/TD]
[TD="align: right"]88.563[/TD]
[TD="align: right"]3.244[/TD]
[TD="align: right"]3.03[/TD]
[TD="align: right"]91.807[/TD]
[TD="align: right"]-0.09517[/TD]
[TD="align: right"]137.7384[/TD]
[TD="align: right"]3.030[/TD]
[TD="align: right"]-3.458[/TD]
[TD="align: right"]85.319[/TD]
[TD="align: right"]-0.33283[/TD]
[TD="align: right"]39.3876[/TD]
[TD="align: right"]-0.333[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.011[/TD]
[TD="align: right"]0.032[/TD]
[TD="align: right"]0.003[/TD]
[TD="align: right"]0.003[/TD]
[TD="align: right"]0.001[/TD]
[TD="align: right"]2.314[/TD]
[TD="align: right"]33.214[/TD]
[TD="align: right"]-0.021[/TD]
[TD="align: right"]2.335[/TD]
[TD="align: right"]33.235[/TD]
[TD="align: right"]2.314013[/TD]
[TD="align: right"]33.21419[/TD]
[TD="align: right"]2.335[/TD]
[TD="align: right"]2.293[/TD]
[TD="align: right"]33.193[/TD]
[TD="align: right"]2.313987[/TD]
[TD="align: right"]33.21381[/TD]
[TD="align: right"]0.000[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.026[/TD]
[TD="align: right"]0.036[/TD]
[TD="align: right"]0.002[/TD]
[TD="align: right"]-0.001[/TD]
[TD="align: right"]0.002[/TD]
[TD="align: right"]-0.422[/TD]
[TD="align: right"]52.257[/TD]
[TD="align: right"]-2.346[/TD]
[TD="align: right"]1.924[/TD]
[TD="align: right"]54.603[/TD]
[TD="align: right"]-0.31668[/TD]
[TD="align: right"]65.29898[/TD]
[TD="align: right"]1.924[/TD]
[TD="align: right"]-2.768[/TD]
[TD="align: right"]49.911[/TD]
[TD="align: right"]-0.52732[/TD]
[TD="align: right"]39.21502[/TD]
[TD="align: right"]-0.527[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.022[/TD]
[TD="align: right"]0.043[/TD]
[TD="align: right"]0.002[/TD]
[TD="align: right"]0.002[/TD]
[TD="align: right"]0.001[/TD]
[TD="align: right"]2.485[/TD]
[TD="align: right"]70.812[/TD]
[TD="align: right"]5.661[/TD]
[TD="align: right"]8.146[/TD]
[TD="align: right"]76.473[/TD]
[TD="align: right"]2.937563[/TD]
[TD="align: right"]83.70815[/TD]
[TD="align: right"]8.146[/TD]
[TD="align: right"]-3.176[/TD]
[TD="align: right"]65.151[/TD]
[TD="align: right"]2.032437[/TD]
[TD="align: right"]57.91585[/TD]
[TD="align: right"]2.032[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]1417[/TD]

[TD="align: right"]-0.055[/TD]
[TD="align: right"]-0.207[/TD]
[TD="align: right"]0.008[/TD]
[TD="align: right"]-0.008[/TD]
[TD="align: right"]0.004[/TD]
[TD="align: right"]11.552[/TD]
[TD="align: right"]107.016[/TD]
[TD="align: right"]9.602[/TD]
[TD="align: right"]21.154[/TD]
[TD="align: right"]116.618[/TD]
[TD="align: right"]12.41354[/TD]
[TD="align: right"]114.9972[/TD]
[TD="align: right"]21.154[/TD]
[TD="align: right"]1.95[/TD]
[TD="align: right"]97.414[/TD]
[TD="align: right"]10.69046[/TD]
[TD="align: right"]99.03484[/TD]
[TD="align: right"]0.000[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]

[TD="align: right"]-0.049[/TD]
[TD="align: right"]-0.188[/TD]
[TD="align: right"]0.006[/TD]
[TD="align: right"]-0.01[/TD]
[TD="align: right"]0.007[/TD]
[TD="align: right"]9.38[/TD]
[TD="align: right"]126.213[/TD]
[TD="align: right"]7.369[/TD]
[TD="align: right"]16.749[/TD]
[TD="align: right"]133.582[/TD]
[TD="align: right"]9.810242[/TD]
[TD="align: right"]132.0021[/TD]
[TD="align: right"]16.749[/TD]
[TD="align: right"]2.011[/TD]
[TD="align: right"]118.844[/TD]
[TD="align: right"]8.949758[/TD]
[TD="align: right"]120.4239[/TD]
[TD="align: right"]0.000[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]

[TD="align: right"]-0.039[/TD]
[TD="align: right"]-0.143[/TD]
[TD="align: right"]0.006[/TD]
[TD="align: right"]-0.003[/TD]
[TD="align: right"]-0.002[/TD]
[TD="align: right"]9.102[/TD]
[TD="align: right"]43.973[/TD]
[TD="align: right"]8.186[/TD]
[TD="align: right"]17.288[/TD]
[TD="align: right"]52.159[/TD]
[TD="align: right"]10.6259[/TD]
[TD="align: right"]51.33518[/TD]
[TD="align: right"]17.288[/TD]
[TD="align: right"]0.916[/TD]
[TD="align: right"]35.787[/TD]
[TD="align: right"]7.578097[/TD]
[TD="align: right"]36.61082[/TD]
[TD="align: right"]0.000[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]

[TD="align: right"]-0.034[/TD]
[TD="align: right"]-0.169[/TD]
[TD="align: right"]0.006[/TD]
[TD="align: right"]-0.014[/TD]
[TD="align: right"]0.004[/TD]
[TD="align: right"]7.062[/TD]
[TD="align: right"]54.59[/TD]
[TD="align: right"]5.994[/TD]
[TD="align: right"]13.056[/TD]
[TD="align: right"]60.584[/TD]
[TD="align: right"]7.720143[/TD]
[TD="align: right"]59.67752[/TD]
[TD="align: right"]13.056[/TD]
[TD="align: right"]1.068[/TD]
[TD="align: right"]48.596[/TD]
[TD="align: right"]6.403857[/TD]
[TD="align: right"]49.50248[/TD]
[TD="align: right"]0.000[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]

[TD="align: right"]-0.054[/TD]
[TD="align: right"]-0.162[/TD]
[TD="align: right"]0.006[/TD]
[TD="align: right"]0.001[/TD]
[TD="align: right"]0.002[/TD]
[TD="align: right"]11.401[/TD]
[TD="align: right"]117.266[/TD]
[TD="align: right"]9.541[/TD]
[TD="align: right"]20.942[/TD]
[TD="align: right"]126.807[/TD]
[TD="align: right"]12.17728[/TD]
[TD="align: right"]125.2504[/TD]
[TD="align: right"]20.942[/TD]
[TD="align: right"]1.86[/TD]
[TD="align: right"]107.725[/TD]
[TD="align: right"]10.62472[/TD]
[TD="align: right"]109.2816[/TD]
[TD="align: right"]0.000[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]1413[/TD]

[TD="align: right"]0.035[/TD]
[TD="align: right"]-0.033[/TD]
[TD="align: right"]-0.001[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.001[/TD]
[TD="align: right"]-2.346[/TD]
[TD="align: right"]3.922[/TD]
[TD="align: right"]2.584[/TD]
[TD="align: right"]0.238[/TD]
[TD="align: right"]6.506[/TD]
[TD="align: right"]-0.64354[/TD]
[TD="align: right"]6.768145[/TD]
[TD="align: right"]0.238[/TD]
[TD="align: right"]-4.93[/TD]
[TD="align: right"]1.338[/TD]
[TD="align: right"]-4.04846[/TD]
[TD="align: right"]1.075855[/TD]
[TD="align: right"]-4.048[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.047[/TD]
[TD="align: right"]-0.044[/TD]
[TD="align: right"]-0.001[/TD]
[TD="align: right"]-0.001[/TD]
[TD="align: right"]-0.001[/TD]
[TD="align: right"]-3.632[/TD]
[TD="align: right"]6.854[/TD]
[TD="align: right"]1.358[/TD]
[TD="align: right"]-2.274[/TD]
[TD="align: right"]8.212[/TD]
[TD="align: right"]-3.36294[/TD]
[TD="align: right"]7.361754[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]-4.99[/TD]
[TD="align: right"]5.496[/TD]
[TD="align: right"]-3.90106[/TD]
[TD="align: right"]6.346246[/TD]
[TD="align: right"]-3.901[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.009[/TD]
[TD="align: right"]-0.008[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.001[/TD]
[TD="align: right"]-0.001[/TD]
[TD="align: right"]-0.097[/TD]
[TD="align: right"]-0.707[/TD]
[TD="align: right"]2.818[/TD]
[TD="align: right"]2.721[/TD]
[TD="align: right"]2.111[/TD]
[TD="align: right"]11.13514[/TD]
[TD="align: right"]81.16026[/TD]
[TD="align: right"]2.721[/TD]
[TD="align: right"]-2.915[/TD]
[TD="align: right"]-3.525[/TD]
[TD="align: right"]-11.3291[/TD]
[TD="align: right"]-82.5743[/TD]
[TD="align: right"]-2.915[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.024[/TD]
[TD="align: right"]-0.028[/TD]
[TD="align: right"]-0.001[/TD]
[TD="align: right"]-0.001[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-1.929[/TD]
[TD="align: right"]3.882[/TD]
[TD="align: right"]3.076[/TD]
[TD="align: right"]1.147[/TD]
[TD="align: right"]6.958[/TD]
[TD="align: right"]0.508346[/TD]
[TD="align: right"]8.787016[/TD]
[TD="align: right"]1.147[/TD]
[TD="align: right"]-5.005[/TD]
[TD="align: right"]0.806[/TD]
[TD="align: right"]-4.36635[/TD]
[TD="align: right"]-1.02302[/TD]
[TD="align: right"]-4.366[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.031[/TD]
[TD="align: right"]-0.025[/TD]
[TD="align: right"]-0.001[/TD]
[TD="align: right"]0.001[/TD]
[TD="align: right"]-0.002[/TD]
[TD="align: right"]-1.837[/TD]
[TD="align: right"]2.41[/TD]
[TD="align: right"]1.055[/TD]
[TD="align: right"]-0.782[/TD]
[TD="align: right"]3.465[/TD]
[TD="align: right"]-1.37516[/TD]
[TD="align: right"]3.015893[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]-2.892[/TD]
[TD="align: right"]1.355[/TD]
[TD="align: right"]-2.29884[/TD]
[TD="align: right"]1.804107[/TD]
[TD="align: right"]-2.299[/TD]

</tbody>

Cenrral Force for MY​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10"]K3[/TH]
[TD="align: left"]=H3+ABS(J3)[/TD]
[/TR]
[TR]
[TH="width: 10"]L3[/TH]
[TD="align: left"]=I3+ABS(J3)[/TD]
[/TR]
[TR]
[TH="width: 10"]M3[/TH]
[TD="align: left"]=H3+ABS(J3^2/I3)[/TD]
[/TR]
[TR]
[TH="width: 10"]N3[/TH]
[TD="align: left"]=I3+ABS(J3^2/H3)[/TD]
[/TR]
[TR]
[TH="width: 10"]O3[/TH]
[TD="align: left"]=IF(AND(K3>0,L3>0),K3,IF(AND(K3<0,L3<0),0,IF(AND(K3<0,L3>0),0,M3)))[/TD]
[/TR]
[TR]
[TH="width: 10"]P3[/TH]
[TD="align: left"]=H3-ABS(J3)[/TD]
[/TR]
[TR]
[TH="width: 10"]Q3[/TH]
[TD="align: left"]=I3-ABS(J3)[/TD]
[/TR]
[TR]
[TH="width: 10"]R3[/TH]
[TD="align: left"]=H3-ABS(J3^2/I3)[/TD]
[/TR]
[TR]
[TH="width: 10"]S3[/TH]
[TD="align: left"]=I3-ABS(J3^2/H3)[/TD]
[/TR]
[TR]
[TH="width: 10"]T3[/TH]
[TD="align: left"]=IF(AND(P3>0,Q3>0),0,IF(AND(P3<0,Q3<0),P3,IF(AND(P3<0,Q3>0),R3,0)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[trustmeasfrnd]

seems either its too easy or too hard.

 

[trustmeasfrnd]

seems either its too easy or too hard.

ok, i was able to do two things first to fill empty cells for plate no. by providing following codes which i am still not sure if it will be general one:-
--------------------------------------------------------------------------------
Sub FillRow()
Dim Name As String
For Each c In Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
If Cells(c.Row, 1) > "" Then
Name = Cells(c.Row, 1).Value
Cells(c.Row, 1).Copy
Else
Cells(c.Row, 1).Value = Name
Cells(c.Row, 1).PasteSpecial (xlPasteFormats)
End If
Next
End Sub
-----------------------------------------------------------------------
This copy empty cells under COLUMN A till value changes or there is empty row ie last row.
Another thing that i was able to sort out was Formula repetition by using below code but i think it can be generalised as well, below is the code that i used,help appreciated to make it better
--------------------------
Sub FillDown()


Dim strFormulas(1 To 5) As Variant


With ThisWorkbook.Sheets("Sheet5")
strFormulas(1) = "=(H3+ABS(J3))"
strFormulas(2) = "=(I3+ABS(J3))"
strFormulas(3) = "=(H3+ABS(J3^2/I3))"
strFormulas(4) = "=(I3+ABS(J3^2/H3))"
strFormulas(5) = "=IF(AND(K3>0,L3>0),K3,IF(AND(K3<0,L3<0),0,IF(AND(K3<0,L3>0),0,M3)))"


.Range("k33").Formula = strFormulas
.Range("k35").FillDown
End With


End Sub
-----------------------------------------------------------
this formula currently has been done for column K3 to O5. Now it also needs to be generalised till last row, please help how to code it better.
there after i will b stuck to sort data with respect to column O.

Any leads will be useful.
thanks and regards.
Abhisekh

 

[Fluff]

This works on the activesheet & replaces both codes you supplied

Sub FillPlate()
   
   Dim strFormulas(1 To 5) As Variant
   
   strFormulas(1) = "=(H3+ABS(J3))"
   strFormulas(2) = "=(I3+ABS(J3))"
   strFormulas(3) = "=(H3+ABS(J3^2/I3))"
   strFormulas(4) = "=(I3+ABS(J3^2/H3))"
   strFormulas(5) = "=IF(AND(K3>0,L3>0),K3,IF(AND(K3<0,L3<0),0,IF(AND(K3<0,L3>0),0,M3)))"

   With Range("A2", Range("B" & Rows.Count).End(xlUp).Offset(, -1))
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
      .Value = .Value
   End With
      
   Range("k3:O" & Range("J" & Rows.Count).End(xlUp).Row).Formula = strFormulas


End Sub

 

[trustmeasfrnd]

Thank you Fluff.
its working and is smaller in coding.

Now i wanted to put a command button above column O which when clicked can sort that column O by increasing value (corresponding to other column cells) and when i again click on it, it sort it by decreasing value -vice versa.
Rows are again variable.

thanks and regards,
Abhisekh

This works on the activesheet & replaces both codes you supplied

Sub FillPlate()
   
   Dim strFormulas(1 To 5) As Variant
   
   strFormulas(1) = "=(H3+ABS(J3))"
   strFormulas(2) = "=(I3+ABS(J3))"
   strFormulas(3) = "=(H3+ABS(J3^2/I3))"
   strFormulas(4) = "=(I3+ABS(J3^2/H3))"
   strFormulas(5) = "=IF(AND(K3>0,L3>0),K3,IF(AND(K3<0,L3<0),0,IF(AND(K3<0,L3>0),0,M3)))"

   With Range("A2", Range("B" & Rows.Count).End(xlUp).Offset(, -1))
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
      .Value = .Value
   End With
      
   Range("k3:O" & Range("J" & Rows.Count).End(xlUp).Row).Formula = strFormulas


End Sub

 

[Fluff]

To do the sort, try recording a macro which can then be cleaned up a bit.

 

[trustmeasfrnd]

Hey Fluff,
i recorded macro and came up with below code.

Hey Fluff,
I am able to sort the values by using below code.
I am sure it can be made more compact and better.
I also used a command button on that worksheet which calls Macro 1 to do sorting.But i am not able to make that sorting to in descending order by clicking on same command button.
i think it can be done by modification in code.

thanks and regards,.
Abhisekh

Sub MySortMacro1()
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A3:AD" & LastRow).Sort Key1:=Range("o3:o" & LastRow), _
       Order1:=xlAscending, Header:=xlNo
End Sub
Sub MySortMacro2()
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A3:AD" & LastRow).Sort Key1:=Range("T3:T" & LastRow), _
       Order1:=xlAscending, Header:=xlNo
End Sub
Sub MySortMacro3()
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A3:AD" & LastRow).Sort Key1:=Range("Y3:Y" & LastRow), _
       Order1:=xlAscending, Header:=xlNo
End Sub
Sub MySortMacro4()
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A3:AD" & LastRow).Sort Key1:=Range("AD3:AD" & LastRow), _
       Order1:=xlAscending, Header:=xlNo
End Sub

To do the sort, try recording a macro which can then be cleaned up a bit.

 

[Fluff]

If the button is on the same sheet as the data & its a form control button, try

Sub MySortMacro1()
   Dim LastRow As Long
   Dim Ord As Long
   Ord = 1
   If ActiveSheet.Buttons(Application.Caller).Caption = "Descending" Then
      Ord = 2
      ActiveSheet.Buttons(Application.Caller).Caption = "Ascending"
   Else
      ActiveSheet.Buttons(Application.Caller).Caption = "Descending"
   End If
   LastRow = Cells(Rows.Count, 1).End(xlUp).Row
   Range("A3:AD" & LastRow).Sort Key1:=Range("o3:o" & LastRow), _
      Order1:=Ord, header:=xlNo
End Sub

 

[trustmeasfrnd]

Thank you Fluff,
Its working.
In post #4 you replied the code that copy formula, is there any way that it copies only values calculated from that formula.
may that will be more harder or have to use do while loop.
Thanks for the help.
regards,
Abhisekh

If the button is on the same sheet as the data & its a form control button, try

Sub MySortMacro1()
   Dim LastRow As Long
   Dim Ord As Long
   Ord = 1
   If ActiveSheet.Buttons(Application.Caller).Caption = "Descending" Then
      Ord = 2
      ActiveSheet.Buttons(Application.Caller).Caption = "Ascending"
   Else
      ActiveSheet.Buttons(Application.Caller).Caption = "Descending"
   End If
   LastRow = Cells(Rows.Count, 1).End(xlUp).Row
   Range("A3:AD" & LastRow).Sort Key1:=Range("o3:o" & LastRow), _
      Order1:=Ord, header:=xlNo
End Sub

 

[Fluff]

Change the last line of the code in post#4 to

With Range("K3:O" & Range("J" & Rows.Count).End(xlUp).Row)
   .Formula = strFormulas
   .Value = .Value
End With