Application Function

[sip8316]

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

 

[Tom Ogilvy]

Assume Column G contains the Monday/Special case entries and that Column A
can be used to determine the extent of the data (It has the name of the
rooms and nothing below the last room name. Adjust as appropriate.

Sub CalcModeforTrash()
Dim rng As Range
Dim v() As Long
Set rng = Range(Range("G2"), _
Range("G" & Cells(Rows.Count, 1).End(xlUp).Row))
ReDim v(1 To rng.Count)
i = 1
For Each cell In rng
Select Case LCase(cell.Text)
Case "once a month"
v(i) = 0.25
Case "7 days a week"
v(i) = 7
Case "once a week"
v(i) = 1
Case Else
v(i) = Application.CountA(cell.Resize(1, 5))
End Select
i = i + 1
Next
Worksheets("Sheet1").Cells(1, 1).Value = Application.Mode(v)
msgbox "results: " & Application.Mode(v)
End Sub


Make sure you look for the answer on the worksheet named Sheet1 in cell
A1.

 

[sip8316]

I keep getting closer and closer but I seem to always have a problem.

I used the code you gave and adapted it to my spreadsheet and it appeared to
work 100% at first, but then I realized that no matter what the data in the
sheet the answer displayed was either 0 or 5. ( So even if I change an
entire row to x on the MWF so the mode should be 3, it would come out 0 or 5.)

I played around alot but I don't have a ton of experience with using VBA so
I wasn't able to fix it. So first off I had a couple of quesions about the
code you provided.

1) This line of code: Set rng = Range(Range("G2"), _
Range("G" & Cells(Rows.Count, 1).End(xlUp).Row))
I interpreted it to be setting the range of data. Saying in column G the
rows from the End all the way up to the top of the sheet is the range to be
used. Is this right? If it is then I should say that my first row of actual
data is the 6th row of the spreadsheet not the 1st.

2) I don't see how, or where it looks at a row and counts the number of x
and uses that number for v(i)...I get the cases for once a month and so on
but not the suming of hte x's.

3) This I didn't bring up before but is there a method of using an array or
something so it only takes the mode of the non zero values. In the past I
have done this with column of numbers by typing:
=MODE(IF(A1:A100<>0,A1:A100))
directly into the cell I wanted it displayed in.

If you know why the mode isn't being calculate correctly I would appreciate
the help. Bellow I typed a very short and simple example of what my spread
sheet looks like....Hopefully that helps some.

And thanks if I can pull this off it will have a huge impact on my intership.



Trash Sweep Mop
Dust
M T W R F M T W R F M T W R F M T W R F
Rm1 x x x x x x x x x x x
Rm2 x x x x x x x x x
Rm3 x x x x x x x x x x x x x x x
Once a Week
Rm4 x x x x x x x x Once a Week
Rm5 Once a Week x x Once a Month Once a Week


With this I want to have the code I right take the mode of each task for all
the rooms (ie. the mode for Trash is 5 times/wk, for Sweep and mop is three
times/wk, and dust would be 1 time/wk because only mode for the non zero
cells is calculated.)

Then I want the mode for each task to be displayed in its respective column.

I have alot of code for this sheet already but this is the only thing I cant
get to work right, if there a way to edit the code you gave me so it
completes my task please let me know

Thanks,

Scott

 

[Tom Ogilvy]

First, I put in data like this:

A x x x x x
B x x x
C once a week
D x x
E x x
F x x
G x x
H once a month
I x x
J x x


It returned 2

I extended the data:

A x x x x x
B x x x
C once a week
D x x
E x x
F x x
G x x
H once a month
I x x
J x x
A 7 days a week
B 7 days a week
C 7 days a week
D 7 days a week
E 7 days a week
F 7 days a week
G 7 days a week
H 7 days a week
I 7 days a week
J 7 days a week
A 7 days a week
B 7 days a week
C 7 days a week
D 7 days a week
E 7 days a week


it returned 7

I put in 29 blank rows in the middle and it returned 0 (as expected).

So it appears to work fine unmodified (based on my stated assumptions).

--
Regards,
Tom Ogilvy

I keep getting closer and closer but I seem to always have a problem.

I used the code you gave and adapted it to my spreadsheet and it appeared to
work 100% at first, but then I realized that no matter what the data in the
sheet the answer displayed was either 0 or 5. ( So even if I change an
entire row to x on the MWF so the mode should be 3, it would come out 0 or 5.)

I played around alot but I don't have a ton of experience with using VBA so
I wasn't able to fix it. So first off I had a couple of quesions about the
code you provided.

1) This line of code: Set rng = Range(Range("G2"), _
Range("G" & Cells(Rows.Count, 1).End(xlUp).Row))
I interpreted it to be setting the range of data. Saying in column G the
rows from the End all the way up to the top of the sheet is the range to be
used. Is this right? If it is then I should say that my first row of actual
data is the 6th row of the spreadsheet not the 1st.

2) I don't see how, or where it looks at a row and counts the number of x
and uses that number for v(i)...I get the cases for once a month and so on
but not the suming of hte x's.

3) This I didn't bring up before but is there a method of using an array or
something so it only takes the mode of the non zero values. In the past I
have done this with column of numbers by typing:
=MODE(IF(A1:A100<>0,A1:A100))
directly into the cell I wanted it displayed in.

If you know why the mode isn't being calculate correctly I would appreciate
the help. Bellow I typed a very short and simple example of what my spread
sheet looks like....Hopefully that helps some.

And thanks if I can pull this off it will have a huge impact on my intership.



Trash Sweep Mop
Dust
M T W R F M T W R F M T W R F M T W R F
Rm1 x x x x x x x x x x x
Rm2 x x x x x x x x x
Rm3 x x x x x x x x x x x x x x x
Once a Week
Rm4 x x x x x x x x Once a Week
Rm5 Once a Week x x Once a Month Once a Week


With this I want to have the code I right take the mode of each task for all
the rooms (ie. the mode for Trash is 5 times/wk, for Sweep and mop is three
times/wk, and dust would be 1 time/wk because only mode for the non zero
cells is calculated.)

Then I want the mode for each task to be displayed in its respective column.

I have alot of code for this sheet already but this is the only thing I cant
get to work right, if there a way to edit the code you gave me so it
completes my task please let me know

Thanks,

Scott

 

[Tom Ogilvy]

Inline:

I keep getting closer and closer but I seem to always have a problem.

I used the code you gave and adapted it to my spreadsheet and it appeared to
work 100% at first, but then I realized that no matter what the data in the
sheet the answer displayed was either 0 or 5. ( So even if I change an
entire row to x on the MWF so the mode should be 3, it would come out 0 or 5.)

I played around alot but I don't have a ton of experience with using VBA so
I wasn't able to fix it. So first off I had a couple of quesions about the
code you provided.

1) This line of code: Set rng = Range(Range("G2"), _
Range("G" & Cells(Rows.Count, 1).End(xlUp).Row))
I interpreted it to be setting the range of data. Saying in column G the
rows from the End all the way up to the top of the sheet is the range to be
used. Is this right? If it is then I should say that my first row of actual
data is the 6th row of the spreadsheet not the 1st.

So change G2 to G6

2) I don't see how, or where it looks at a row and counts the number of x
and uses that number for v(i)...I get the cases for once a month and so on
but not the suming of hte x's.

counts them right here:
v(i) = Application.CountA(cell.Resize(1, 5))

3) This I didn't bring up before but is there a method of using an array or
something so it only takes the mode of the non zero values. In the past I
have done this with column of numbers by typing:
=MODE(IF(A1:A100<>0,A1:A100))
directly into the cell I wanted it displayed in.

Just change

to

Dim v() as Variant

and change

v(i) = Application.CountA(cell.Resize(1, 5))

to

if application.CountA(cell.Resize(1,5)) = 0 then
v(i) = False
else
v(i) = countA(cell.Resize(1,5))
End if

From help on the Mode Function:
If an array or reference argument contains text, logical values, or empty
cells, those values are ignored; however, cells with the value zero are
included.

If you know why the mode isn't being calculate correctly I would appreciate
the help. Bellow I typed a very short and simple example of what my spread
sheet looks like....Hopefully that helps some.

And thanks if I can pull this off it will have a huge impact on my intership.



Trash Sweep Mop
Dust
M T W R F M T W R F M T W R F M T W R F
Rm1 x x x x x x x x x x x
Rm2 x x x x x x x x x
Rm3 x x x x x x x x x x x x x x x
Once a Week
Rm4 x x x x x x x x Once a Week
Rm5 Once a Week x x Once a Month Once a Week


With this I want to have the code I right take the mode of each task for all
the rooms (ie. the mode for Trash is 5 times/wk, for Sweep and mop is three
times/wk, and dust would be 1 time/wk because only mode for the non zero
cells is calculated.)

Then I want the mode for each task to be displayed in its respective

column.

That could be done, but unfortunately I can't guess where your data is or
where you want the results.

You show five columns for each activity and you say you have a variable
number of rows - what does in its respective column mean specifically.
Where are the respective columns. What row in relation to the bottom of the
data. What cell contains the first M in M T W R F columns.

I have alot of code for this sheet already but this is the only thing I cant
get to work right, if there a way to edit the code you gave me so it
completes my task please let me know

Thanks,

Scott

 

[sip8316]

Tom thanks,

I dont' know why I couldn't make it work the first time I tried but I re
pasted and copied and after adjusting it to my project it worked great And
the new code to not include blank cells worked great also. Sorry for all the
trouble,

And thanks,

Scott

Inline:

I keep getting closer and closer but I seem to always have a problem.

I used the code you gave and adapted it to my spreadsheet and it appeared to
work 100% at first, but then I realized that no matter what the data in the
sheet the answer displayed was either 0 or 5. ( So even if I change an
entire row to x on the MWF so the mode should be 3, it would come out 0 or 5.)

I played around alot but I don't have a ton of experience with using VBA so
I wasn't able to fix it. So first off I had a couple of quesions about the
code you provided.

1) This line of code: Set rng = Range(Range("G2"), _
Range("G" & Cells(Rows.Count, 1).End(xlUp).Row))
I interpreted it to be setting the range of data. Saying in column G the
rows from the End all the way up to the top of the sheet is the range to be
used. Is this right? If it is then I should say that my first row of actual
data is the 6th row of the spreadsheet not the 1st.

So change G2 to G6

2) I don't see how, or where it looks at a row and counts the number of x
and uses that number for v(i)...I get the cases for once a month and so on
but not the suming of hte x's.

counts them right here:
v(i) = Application.CountA(cell.Resize(1, 5))

3) This I didn't bring up before but is there a method of using an array or
something so it only takes the mode of the non zero values. In the past I
have done this with column of numbers by typing:
=MODE(IF(A1:A100<>0,A1:A100))
directly into the cell I wanted it displayed in.

Just change

to

Dim v() as Variant

and change

v(i) = Application.CountA(cell.Resize(1, 5))

to

if application.CountA(cell.Resize(1,5)) = 0 then
v(i) = False
else
v(i) = countA(cell.Resize(1,5))
End if

From help on the Mode Function:
If an array or reference argument contains text, logical values, or empty
cells, those values are ignored; however, cells with the value zero are
included.

If you know why the mode isn't being calculate correctly I would appreciate
the help. Bellow I typed a very short and simple example of what my spread
sheet looks like....Hopefully that helps some.

And thanks if I can pull this off it will have a huge impact on my intership.



Trash Sweep Mop
Dust
M T W R F M T W R F M T W R F M T W R F
Rm1 x x x x x x x x x x x
Rm2 x x x x x x x x x
Rm3 x x x x x x x x x x x x x x x
Once a Week
Rm4 x x x x x x x x Once a Week
Rm5 Once a Week x x Once a Month Once a Week


With this I want to have the code I right take the mode of each task for all
the rooms (ie. the mode for Trash is 5 times/wk, for Sweep and mop is three
times/wk, and dust would be 1 time/wk because only mode for the non zero
cells is calculated.)

Then I want the mode for each task to be displayed in its respective

column.

That could be done, but unfortunately I can't guess where your data is or
where you want the results.

You show five columns for each activity and you say you have a variable
number of rows - what does in its respective column mean specifically.
Where are the respective columns. What row in relation to the bottom of the
data. What cell contains the first M in M T W R F columns.