Can you help me to shorten this code? Thanks a lot

[saftawy1]

test-total column.xlsb
	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
1	p19	p20	p21	p22	p23	p24	p25	p26	p27	p28	p29	p30	p31	p32	p33	p34	p35	p36	p37	p38	p39
2	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
3	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2
4	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3
5	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4
6	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5
7	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6
data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P2:AM7	Expression	=$AT2="هديه"	text	NO
P2:AM7	Expression	=$AT2="مقعد مجانى"	text	NO
P2:AM7	Expression	=$AT2="مجمد"	text	NO
P2:AM7	Expression	=$AT2="comp"	text	NO
P2:AM7	Expression	=$AT2="pc"	text	NO
P2:AM7	Expression	=$AT2="cxl"	text	NO
P2:AM7	Expression	=$AT2="cash"	text	NO

Me.ListBox1.AddItem "Total"

 Dim sum1 As Double, sum2 As Double, sum3 As Double, sum4 As Double, sum5 As Double, sum6 As Double, sum7 As Double, sum8 As Double, sum9 As Double, sum10 As Double, _
 sum11 As Double, sum12 As Double, sum13 As Double, sum14 As Double, sum15 As Double, sum16 As Double, sum17 As Double, sum18 As Double, sum19 As Double, sum20 As Double
 
 For i = 1 To ListBox1.ListCount - 1
 
sum1 = sum1 + Val(Me.ListBox1.List(i, 1))
sum2 = sum2 + Val(Me.ListBox1.List(i, 2))
sum3 = sum3 + Val(Me.ListBox1.List(i, 3))
sum4 = sum4 + Val(Me.ListBox1.List(i, 4))
sum5 = sum5 + Val(Me.ListBox1.List(i, 5))
sum6 = sum6 + Val(Me.ListBox1.List(i, 6))
sum7 = sum7 + Val(Me.ListBox1.List(i, 7))
sum8 = sum8 + Val(Me.ListBox1.List(i, 8))
sum9 = sum9 + Val(Me.ListBox1.List(i, 9))
sum10 = sum10 + Val(Me.ListBox1.List(i, 10))
sum11 = sum11 + Val(Me.ListBox1.List(i, 11))
sum12 = sum12 + Val(Me.ListBox1.List(i, 12))
sum13 = sum13 + Val(Me.ListBox1.List(i, 13))
sum14 = sum14 + Val(Me.ListBox1.List(i, 14))
sum15 = sum15 + Val(Me.ListBox1.List(i, 15))
sum16 = sum16 + Val(Me.ListBox1.List(i, 16))
sum17 = sum17 + Val(Me.ListBox1.List(i, 17))
sum18 = sum18 + Val(Me.ListBox1.List(i, 18))
sum19 = sum19 + Val(Me.ListBox1.List(i, 19))
sum20 = sum20 + Val(Me.ListBox1.List(i, 20))

Me.ListBox1.List(ListBox1.ListCount - 1, 1) = sum1 & ".00"
Me.ListBox1.List(ListBox1.ListCount - 1, 2) = sum2 & ".00"
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = sum3 & ".00"
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = sum4 & ".00"
Me.ListBox1.List(ListBox1.ListCount - 1, 5) = sum5 & ".00"
Me.ListBox1.List(ListBox1.ListCount - 1, 6) = sum6 & ".00"
Me.ListBox1.List(ListBox1.ListCount - 1, 7) = sum7 & ".00"
Me.ListBox1.List(ListBox1.ListCount - 1, 8) = sum8 & ".00"
Me.ListBox1.List(ListBox1.ListCount - 1, 9) = sum9 & ".00"
Me.ListBox1.List(ListBox1.ListCount - 1, 10) = sum10 & ".00"
Me.ListBox1.List(ListBox1.ListCount - 1, 11) = sum11 & ".00"
Me.ListBox1.List(ListBox1.ListCount - 1, 12) = sum12 & ".00"
Me.ListBox1.List(ListBox1.ListCount - 1, 13) = sum13 & ".00"
Me.ListBox1.List(ListBox1.ListCount - 1, 14) = sum14 & ".00"
Me.ListBox1.List(ListBox1.ListCount - 1, 15) = sum15 & ".00"
Me.ListBox1.List(ListBox1.ListCount - 1, 16) = sum16 & ".00"
Me.ListBox1.List(ListBox1.ListCount - 1, 17) = sum17 & ".00"
Me.ListBox1.List(ListBox1.ListCount - 1, 18) = sum18 & ".00"
Me.ListBox1.List(ListBox1.ListCount - 1, 19) = sum19 & ".00"
Me.ListBox1.List(ListBox1.ListCount - 1, 20) = sum20 & ".00"

Next i

 

[johnnyL]

How about:

    Me.ListBox1.AddItem "Total"
'
    Dim i                   As Long
    Dim J                   As Long
    Dim SumArray(1 To 20)   As Variant
'
    For i = 1 To ListBox1.ListCount - 1
        For J = 1 To 20
            SumArray(J) = SumArray(J) + Val(Me.ListBox1.List(i, J))
'
            Me.ListBox1.List(ListBox1.ListCount - 1, J) = SumArray(J) & ".00"
        Next J
    Next i

After that instead of variable called sum1 ... use SumArray(1)
For the variable called sum2 ... use SumArray(2)
etc.

 

[saftawy1]

How about:

    Me.ListBox1.AddItem "Total"
'
    Dim i                   As Long
    Dim J                   As Long
    Dim SumArray(1 To 20)   As Variant
'
    For i = 1 To ListBox1.ListCount - 1
        For J = 1 To 20
            SumArray(J) = SumArray(J) + Val(Me.ListBox1.List(i, J))
'
            Me.ListBox1.List(ListBox1.ListCount - 1, J) = SumArray(J) & ".00"
        Next J
    Next i

After that instead of variable called sum1 ... use SumArray(1)
For the variable called sum2 ... use SumArray(2)
etc.

thank you johnnyl very much its very good

 

[johnnyL]

Glad to help.