match and arrsum problem

oddworld

Board Regular
Joined
May 31, 2005
Messages
248
hi all i ahve a table with ranks in colum "e", each row has will have a number one in either column "h" through to "L"
i am trying to look at colum "e" count the rank that the number one in any of the columns "h-l" and summarise the data on sheet "summary"
i can not fo the life of me figure out the "C=MATCH or IF ARRSUM" part of the below code.
if anyone has any advice it would be appreciated.
Dim total(20, 4) As String
Dim row As Integer, c As Integer

total(1, 0) = "PTE"
total(2, 0) = "CFN"
total(3, 0) = "SPR"
toatl(4, 0) = "LCPL"
toatl(5, 0) = "LCPL"
toatl(6, 0) = "LBDR"
toatl(7, 0) = "CPL"
toatl(8, 0) = "BDR"
toatl(9, 0) = "SGT"
toatl(10, 0) = "WO2"
toatl(11, 0) = "WO1"
toatl(12, 0) = "LT"
toatl(13, 0) = "CAPT"
toatl(14, 0) = "MAJ"
toatl(15, 0) = "LTCOL"

For row = 1 To Range("e65000").End(xlUp).row
c = match (1,"c"& row&"L" & row)
Select Case Cells("e" & row)

Case "PTE", "CFN", "SPR"
total(1, c) = toatal(1, c) + 1
Case "LCPL", "LBDR"
total(2, c) = toatal(2, c) + 1
Case "BDR", "CPL"
total(3, c) = toatal(3, c) + 1
Case "SGT"
total(4, c) = toatal(4, c) + 1
Case "WO2"
total(5, c) = toatal(5, c) + 1
Case "WO1"
total(6, c) = toatal(6, c) + 1
Case "LT"
total(7, c) = toatal(7, c) + 1
Case "CAPT"
total(8, c) = toatal(8, c) + 1
Case "MAJ"
total(9, c) = toatal(9, c) + 1
Case "LTCOL"
total(10, c) = toatal(10, c) + 1

End Select
Next row

row = 40
For Rank = 1 To 20

IF ARRSUM (TOTAL(RANK))>0
Sheets("summary").Cells(ro, 1) = toatl(Rank, 0)
for svc = 1-4
Sheets
("summary").cells(row,svc+1)=total(rank,svc)
Next svc

End If
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

1) c=match...
the variable c gets the result of the match function. It is getting the place on row i, between columns C and L of the value 1.

2) Is there some code somewhere that has a function called ARRSUM? Or is it an external addin? My guess is that it is summing the values in the array TOTAL, for the nominated RANK.

So if RANK is 1 (first iteration of the loop), then it would sum total(1,1),total(1,2)....total(1,?) and if the result is >0, then it outputs the value of total(1,0) to Summary!A40.


HTH

Tony
 

Forum statistics

Threads
1,141,587
Messages
5,707,236
Members
421,498
Latest member
matinebi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top