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
1			Shear		Membrane			Bending Moment			Mxd For TOP REINFORCEMENT					Mxd For BOTTOM REINFORCEMENT
2	Plate	L/C	SQX (local) N/mm2	SQY (local) N/mm2	SX (local) N/mm2	SY (local) N/mm2	SXY (local) N/mm2	Mx kNm/m	My kNm/m	Mxy kNm/m	Mx1	My1	Mx2	My2	Mxd	Mx1	My1	Mx2	My2	Mxd
3	1422	301 1.5DL1 + 1.5LL	0.016	0.028				-0.183	6.739	0.039	-0.144	6.778	-0.18277	6.747311	0.000	-0.222	6.7	-0.18323	6.730689	-0.183
4		302 1.2DL1 + 1.2LL + 1.2WLX (W-E)	0.024	0.033	-0.001	-0.001	-0.001	-1.071	7.951	0.552	-0.519	8.503	-1.03268	8.235504	0.000	-1.623	7.399	-1.10932	7.666496	-1.109
5		303 1.2DL1 + 1.2LL - 1.2WLX (W-E)	0.001	0.011	0.001			0.799	2.813	-0.489	1.288	3.302	0.884006	3.112275	1.288	0.31	2.324	0.713994	2.513725	0.000
6		304 1.2DL1 + 1.2LL + 1.2WLZ (N-S)	0.011	0.02				-0.171	4.031	-0.705	0.534	4.736	-0.0477	6.937579	0.534	-0.876	3.326	-0.2943	1.124421	-0.294
7		305 1.2DL1 + 1.2LL - 1.2WLZ (N-S)	0.014	0.024				-0.07	6.806	0.811	0.741	7.617	0.026638	16.20201	0.741	-0.881	5.995	-0.16664	-2.59001	-0.167
8		310 1.5DL1 + 1.5WLX (W-E)	0.029	0.04	-0.001	-0.001	-0.001	-1.266	9.638	0.595	-0.671	10.233	-1.22927	9.917641	0.000	-1.861	9.043	-1.30273	9.358359	-1.303
9	1419	301 1.5DL1 + 1.5LL	0.03	0.05	0.002		0.002	1.289	76.493	1.941	3.23	78.434	1.338253	79.41579	3.230	-0.652	74.552	1.239747	73.57021	1.240
10		302 1.2DL1 + 1.2LL + 1.2WLX (W-E)	0.037	0.047	0.001	-0.002	0.002	-0.214	88.563	3.244	3.03	91.807	-0.09517	137.7384	3.030	-3.458	85.319	-0.33283	39.3876	-0.333
11		303 1.2DL1 + 1.2LL - 1.2WLX (W-E)	0.011	0.032	0.003	0.003	0.001	2.314	33.214	-0.021	2.335	33.235	2.314013	33.21419	2.335	2.293	33.193	2.313987	33.21381	0.000
12		304 1.2DL1 + 1.2LL + 1.2WLZ (N-S)	0.026	0.036	0.002	-0.001	0.002	-0.422	52.257	-2.346	1.924	54.603	-0.31668	65.29898	1.924	-2.768	49.911	-0.52732	39.21502	-0.527
13		305 1.2DL1 + 1.2LL - 1.2WLZ (N-S)	0.022	0.043	0.002	0.002	0.001	2.485	70.812	5.661	8.146	76.473	2.937563	83.70815	8.146	-3.176	65.151	2.032437	57.91585	2.032
14	1417	301 1.5DL1 + 1.5LL	-0.055	-0.207	0.008	-0.008	0.004	11.552	107.016	9.602	21.154	116.618	12.41354	114.9972	21.154	1.95	97.414	10.69046	99.03484	0.000
15		302 1.2DL1 + 1.2LL + 1.2WLX (W-E)	-0.049	-0.188	0.006	-0.01	0.007	9.38	126.213	7.369	16.749	133.582	9.810242	132.0021	16.749	2.011	118.844	8.949758	120.4239	0.000
16		303 1.2DL1 + 1.2LL - 1.2WLX (W-E)	-0.039	-0.143	0.006	-0.003	-0.002	9.102	43.973	8.186	17.288	52.159	10.6259	51.33518	17.288	0.916	35.787	7.578097	36.61082	0.000
17		304 1.2DL1 + 1.2LL + 1.2WLZ (N-S)	-0.034	-0.169	0.006	-0.014	0.004	7.062	54.59	5.994	13.056	60.584	7.720143	59.67752	13.056	1.068	48.596	6.403857	49.50248	0.000
18		305 1.2DL1 + 1.2LL - 1.2WLZ (N-S)	-0.054	-0.162	0.006	0.001	0.002	11.401	117.266	9.541	20.942	126.807	12.17728	125.2504	20.942	1.86	107.725	10.62472	109.2816	0.000
19	1413	301 1.5DL1 + 1.5LL	0.035	-0.033	-0.001		-0.001	-2.346	3.922	2.584	0.238	6.506	-0.64354	6.768145	0.238	-4.93	1.338	-4.04846	1.075855	-4.048
20		302 1.2DL1 + 1.2LL + 1.2WLX (W-E)	0.047	-0.044	-0.001	-0.001	-0.001	-3.632	6.854	1.358	-2.274	8.212	-3.36294	7.361754	0.000	-4.99	5.496	-3.90106	6.346246	-3.901
21		303 1.2DL1 + 1.2LL - 1.2WLX (W-E)	0.009	-0.008		0.001	-0.001	-0.097	-0.707	2.818	2.721	2.111	11.13514	81.16026	2.721	-2.915	-3.525	-11.3291	-82.5743	-2.915
22		304 1.2DL1 + 1.2LL + 1.2WLZ (N-S)	0.024	-0.028	-0.001	-0.001		-1.929	3.882	3.076	1.147	6.958	0.508346	8.787016	1.147	-5.005	0.806	-4.36635	-1.02302	-4.366
23		305 1.2DL1 + 1.2LL - 1.2WLZ (N-S)	0.031	-0.025	-0.001	0.001	-0.002	-1.837	2.41	1.055	-0.782	3.465	-1.37516	3.015893	0.000	-2.892	1.355	-2.29884	1.804107	-2.299

<tbody>
</tbody>

Cenrral Force for MY​

Worksheet Formulas Cell Formula K3 =H3+ABS(J3) L3 =I3+ABS(J3) M3 =H3+ABS(J3^2/I3) N3 =I3+ABS(J3^2/H3) O3 =IF(AND(K3>0,L3>0),K3,IF(AND(K3<0,L3<0),0,IF(AND(K3<0,L3>0),0,M3))) P3 =H3-ABS(J3) Q3 =I3-ABS(J3) R3 =H3-ABS(J3^2/I3) S3 =I3-ABS(J3^2/H3) T3 =IF(AND(P3>0,Q3>0),0,IF(AND(P3<0,Q3<0),P3,IF(AND(P3<0,Q3>0),R3,0))) <tbody> </tbody>

<tbody>
</tbody>

 

[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