I am running the followig line of code to calculate the mode(and a similar
line to calculate the average) of a series of data. Just some background, I
have the sheet set up with task in columns, with sub columns MTWRF for the
days of the week. I have rooms set up in the rows. Then where a row and
column intersect an x is placed if the task is completed in that room on the
corresponding day.

The following line of code seems to correctly take the sum of the number of
times per week (based on the number of x's in the row) a task is done for
each respective room and then find the mode for all the rooms.

I have atleast three quesions about that:

1) Using this code I can only have it find the mode of 12 rows at a time, I
need to be able to take the mode of anywhere from 5 to a little over 100
rows. Is that possible?

2) Right now it is set up so a message box appears and displays "Mode for
Trash...." I tried to instead set up to display the mode in a cell on the
spread sheet, but when I used the code
Worksheets("Sheet1").Cells(1,1).Value=lmode, no value appeared in cell A1.
Is there something Im doing wrong.

3)Finally, In some of the rooms a task is done only once a week, once a
month, twice a week or 7 times a week. When that is the case in the cell
that corresponds to Monday for the respective task and room one of those
special cases is entered in (ie "Once a Month", "7 days a Week", "Once a
Week") Also each of these cases results in a set sum for the task in a room.
(ie once a week=1 for the sum, once a mont=.25, 7 days a week =7.) I
thought about using if statements to take that into consideration but I don't
know how that would work using the Application function that I am, If you
have any ideas then let me know.

Scott

Sub CalcModeforTrash()
Dim lMode As Long
Dim v(1 To 13) As Long
i = 1
For Each cell In Range("G2:G14")
v(i) = Application.CountA(cell.Resize(1, 5))
i = i + 1
Next
lMode = Application.Mode(v)
MsgBox "Mode for Trash: " & lMode
End Sub