Help with If statement / WorksheetFunction.Mode error

[JR_Chicago]

When I try to run the macro containing the following code an "Application-defined or object-defined error" is generated

Code:

If Not IsNumeric(Application.WorksheetFunction.Mode(stock1)) Then
stock1Mode = "No Mode"
ElseIf IsNumeric(Application.WorksheetFunction.Mode(stock1)) Then
stock1Mode = Application.WorksheetFunction.Mode(stock1)
End If

If you look at the code you will see that I am trying to have the macro return a string "No Mode" when the array on which I am using the mode function does not contain any numerical elements which are the same, but when there are two or more identical numerical elements, the macro should return the mode of the array.

When I step through the code the error is generated on the second line. Apparently it doesn't like "stock1Mode = "No Mode"... Any suggestions would be greatly appreciated because I am at my wits end!

 

[Norie]

What is the error message?

Are you sure that Application.WorksheetFunction.Mode... is the problem?

 

[JR_Chicago]

"Application-defined or object-defined error"
When there are two or more elements in the array that are identical (i.e. a mode exists) the mode function works fine...

The problem is, my arrays don't have similar elements, but they might in the future, so I need the macro to be able to handle both scenarios (if a mode exists then return the mode, else return the string "No Mode")

I'm relatively new to VBA and no a programmer by trade so I've been unable so far to debug this error.

Heres an array of 10 non-identical numerical elements to work on... If anyone can write something that does what I'm trying to do, I'd really appreciate it!

<TABLE style="WIDTH: 480pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=640 border=0><COLGROUP><COL style="WIDTH: 48pt" span=10 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ccffcc" width=64 height=17>6685</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64>3342</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64>4122</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64>7593</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64>554</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64>3110</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64>4446</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64>5486</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64>8034</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64>1945</TD></TR></TBODY></TABLE>

 

[dscg]

Here is one way to handle errors like this. If your data is in range A1:A10:

Option Explicit
Sub Marine()
Dim stock1 As Variant
Dim stock1Mode As Variant

stock1 = Application.Transpose(Range("A1:A10"))

On Error Resume Next
stock1Mode = Application.WorksheetFunction.Mode(stock1)
If Err.Number = 0 Then
    Application.WorksheetFunction.Mode (stock1)
Else: stock1Mode = "No Mode"
End If
On Error GoTo 0

MsgBox stock1Mode

End Sub

 

[JR_Chicago]

That worked perfectly! thanks dscg!

 

[JR_Chi]

dscg

I sent you the code in a private message... but I just realized that you weren't the person who'd requested I'd do that so... sorry for the spam :-/