Application.Mode

[sip8316]

I am creating a spreadsheet that Loops through a series of continuous row,
assiging a variant variable, V(i), a specific value based on the text within
the respective cell. I then want to take the mode of all V(i) values. I
have already set up the code so it will set v(i) and tested it so I know it
works but when I try to calculate the mode I get errors.

I have tried using cells(1,1).value=application.mode(v) and
cells(1,1)=application.mode(v) but it always returns a #N/A in the cell. Is
there a different function I should be using? Below is some example of my
code:


Dim V() as variant

Select Case LCase(cell.Text)
Case "once a month"
V(i) = 0.25
Case "one time a month"
V(i) = 0.25
Case Else
If Application.CountA(cell.Resize(1, 5)) = 0 Then
V(i) = False
Else
V(i) = Application.CountA(cell.Resize(1, 5))
End If
End Select

Cells(CurrentRow + 2, CurrentColumn) = Application.Mode(V)
Cells(CurrentRow + 2, CurrentColumn).Resize(1, 5).Merge


Thanks for the Help,

Scott

 

[keepITcool]

if no item occurs more than once in the array
the MODE functions returns an NA# error.
(there is no most frequent occcurance)



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


sip8316 wrote :

 

[sip8316]

Thanks,

But I thought of that too and checked it and there is definately a number
that is displayed significatley more times than the others. So I still don't
know what the problem is.

Scott

 

[keepITcool]

then post complete code..
I cant see you checking it in the snippet you posted.

following test works for me:
Sub TestMode()
Dim V
ReDim V(1 To 5)
V(1) = 3
V(2) = 8
V(3) = 3
V(5) = "text" 'strings are ignored
MsgBox CStr(Application.Mode(V))

V(3) = 1
MsgBox CStr(Application.Mode(V))
End Sub



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


sip8316 wrote :

 

[sip8316]

Sorry,

I checked it by having the value for V(i) displayed in the rows beneth the
actual data, then I could look at the value for v(i) and see if it coresponds
correctly to the cells I was referenceing. Heres the same code though with a
little more detail and with the loops added in.


Dim v(i) as variant
Dim CurrentColumn as Integer
Dim columnCounter as Integer
Dim rngOneColumn as Range
Dim CurrentRow as Integer


While (Cells(5, CurrentColumn) <> "")
ReDim V(1 To ColumnCounter)
CurrentRow = 6
i = 6
For Each cell In rngOneColumn
Select Case LCase(cell.Text)
Case "once a month"
V(i) = 0.25
Case "one time a month"
V(i) = 0.25
Case "7 days a week"
V(i) = 7
Case Else
If Application.CountA(cell.Resize(1, 5)) = 0 Then
V(i) = False
Else
V(i) = Application.CountA(cell.Resize(1, 5))
End If

End Select
CurrentRow = CurrentRow + 1
Next
Cells(CurrentRow + 2, CurrentColumn) = Application.Mode(V)
Cells(CurrentRow + 2, CurrentColumn).Resize(1, 5).Merge
Wend


This code would loop through a given number of columns and take the mode of
the V(i) values of the all the cells in a respective column. So I will end
up with many modes, one for each column.

I checked it by adding code such as this:

Cells(VDisplayerRow, VDisplayerCol) = V(i)


In the loops I had those two variables set up so they progressed with the
rest of the calculations. In other words if I had 10 rows and 10 columns of
data, the v value of cell(1,1) was displayed in (11,1)....cell(1,2) in
(11,2)....cell(1,3) in (11,3)....and in a similar maner cell(2,1) in
(12,1).....cell(3,1) in (13,1) When I did this I checked all of the v(i)
values and they were all correct. However when I tried to uses
application.mode(V) I recieved #N/A for the mode of each column.

Thanks again,

Scott

 

[keepITcool]

Scott,
could you please mail your workbook?

cant figure this out from your example
but without the actual data.

my email is below.. just add the @ and the .

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


sip8316 wrote :