Hello. I'm attempting to cycle through an array, looking for a color (sPat).
If found, return the corresponding iColorIndex number. For example, if sPat =
"Dark Red", then iColorIndex should be 9. Where am I going wrong (probably in
the variable types)? Thanks. Jason

Sub FindIndex()

Dim ColorArray(1 To 2)
Dim sPat As String
Dim iColorIndex As Integer

sPat = "Dark Red"
ColorArray(1) = Array(1, 9, 3)
ColorArray(2) = Array("Black", "Dark Red", "Red")

For j = 0 To 2
If ColorArray(2)(j) = sPat Then
iColorIndex = ColorArray(1)(j)
End If
Exit For
Next
Debug.Print iColorIndex

End Sub

I would think you'd only want to exit that loop if you found a match:

If ColorArray(2)(j) = sPat Then
iColorIndex = ColorArray(1)(j)
Exit For
End If

You may want to use some kind of indicator (a boolean variable or a default
icolorindex out of the range) to indicate if a match was found:

Option Explicit
Sub FindIndex()

Dim ColorArray(1 To 2)
Dim sPat As String
Dim iColorIndex As Long
Dim j As Long

sPat = "Dark Red"
ColorArray(1) = Array(1, 9, 3)
ColorArray(2) = Array("Black", "Dark Red", "Red")

iColorIndex = 9999
For j = 0 To 2
If ColorArray(2)(j) = sPat Then
iColorIndex = ColorArray(1)(j)
Exit For
End If
Next j
if icolorindex = 9999 then
debug.print "not found"
else
Debug.Print iColorIndex
end if

End Sub

Jason Morin wrote:

Hello. I'm attempting to cycle through an array, looking for a color (sPat).
If found, return the corresponding iColorIndex number. For example, if sPat =
"Dark Red", then iColorIndex should be 9. Where am I going wrong (probably in
the variable types)? Thanks. Jason

Sub FindIndex()

Dim ColorArray(1 To 2)
Dim sPat As String
Dim iColorIndex As Integer

sPat = "Dark Red"
ColorArray(1) = Array(1, 9, 3)
ColorArray(2) = Array("Black", "Dark Red", "Red")

For j = 0 To 2
If ColorArray(2)(j) = sPat Then
iColorIndex = ColorArray(1)(j)
End If
Exit For
Next
Debug.Print iColorIndex

End Sub

--

Dave Peterson

Comment out Exit For in the For loop.

Regards,
p.

Hi Jason,

Good to see you back again, even if you aren't answering workheet function
questions.

You can do this with a worksheet function, albeit in VBA

Sub FindIndex()
Dim ColorArray(1 To 2)
Dim sPat As String
Dim iColorIndex As Long
Dim j As Long

sPat = "Dark Red"
ColorArray(1) = Array(1, 9, 3)
ColorArray(2) = Array("Black", "Dark Red", "Red")

On Error Resume Next
j = Application.Match(sPat, ColorArray(2), 0)
On Error GoTo 0
If Err.Number = 0 Then
iColorIndex = ColorArray(1)(j - 1)
End If

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jason Morin" wrote in message
...
Hello. I'm attempting to cycle through an array, looking for a color
(sPat).
If found, return the corresponding iColorIndex number. For example, if
sPat =
"Dark Red", then iColorIndex should be 9. Where am I going wrong (probably
in
the variable types)? Thanks. Jason

Sub FindIndex()

Dim ColorArray(1 To 2)
Dim sPat As String
Dim iColorIndex As Integer

sPat = "Dark Red"
ColorArray(1) = Array(1, 9, 3)
ColorArray(2) = Array("Black", "Dark Red", "Red")

For j = 0 To 2
If ColorArray(2)(j) = sPat Then
iColorIndex = ColorArray(1)(j)
End If
Exit For
Next
Debug.Print iColorIndex

End Sub

Hi Bob-

Yep, good to be back. I'm with a new company in a senior role, back doing
what I love. I'll get back into answering questions soon in functions, but
I'll be posting lots of questions in programming, too.

Thanks for the help.
Jason

"Bob Phillips" wrote:

Hi Jason,

Good to see you back again, even if you aren't answering workheet function
questions.

You can do this with a worksheet function, albeit in VBA

Sub FindIndex()
Dim ColorArray(1 To 2)
Dim sPat As String
Dim iColorIndex As Long
Dim j As Long

sPat = "Dark Red"
ColorArray(1) = Array(1, 9, 3)
ColorArray(2) = Array("Black", "Dark Red", "Red")

On Error Resume Next
j = Application.Match(sPat, ColorArray(2), 0)
On Error GoTo 0
If Err.Number = 0 Then
iColorIndex = ColorArray(1)(j - 1)
End If

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jason Morin" wrote in message
...
Hello. I'm attempting to cycle through an array, looking for a color
(sPat).
If found, return the corresponding iColorIndex number. For example, if
sPat =
"Dark Red", then iColorIndex should be 9. Where am I going wrong (probably
in
the variable types)? Thanks. Jason

Sub FindIndex()

Dim ColorArray(1 To 2)
Dim sPat As String
Dim iColorIndex As Integer

sPat = "Dark Red"
ColorArray(1) = Array(1, 9, 3)
ColorArray(2) = Array("Black", "Dark Red", "Red")

For j = 0 To 2
If ColorArray(2)(j) = sPat Then
iColorIndex = ColorArray(1)(j)
End If
Exit For
Next
Debug.Print iColorIndex

End Sub

I think Bob meant to use one of these:

Dim j As Variant
.....
j = Application.Match(sPat, ColorArray(2), 0)
If IsError(j) Then
'not found
Else
iColorIndex = ColorArray(1)(j - 1)
End If

or...

Dim j as Long
.....
On Error Resume Next
j = Application.WorksheetFunction.Match(sPat, ColorArray(2), 0)
On Error GoTo 0
If Err.Number = 0 Then
iColorIndex = ColorArray(1)(j - 1)
End If

Bob Phillips wrote:

Hi Jason,

Good to see you back again, even if you aren't answering workheet function
questions.

You can do this with a worksheet function, albeit in VBA

Sub FindIndex()
Dim ColorArray(1 To 2)
Dim sPat As String
Dim iColorIndex As Long
Dim j As Long

sPat = "Dark Red"
ColorArray(1) = Array(1, 9, 3)
ColorArray(2) = Array("Black", "Dark Red", "Red")

On Error Resume Next
j = Application.Match(sPat, ColorArray(2), 0)
On Error GoTo 0
If Err.Number = 0 Then
iColorIndex = ColorArray(1)(j - 1)
End If

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jason Morin" wrote in message
...
Hello. I'm attempting to cycle through an array, looking for a color
(sPat).
If found, return the corresponding iColorIndex number. For example, if
sPat =
"Dark Red", then iColorIndex should be 9. Where am I going wrong (probably
in
the variable types)? Thanks. Jason

Sub FindIndex()

Dim ColorArray(1 To 2)
Dim sPat As String
Dim iColorIndex As Integer

sPat = "Dark Red"
ColorArray(1) = Array(1, 9, 3)
ColorArray(2) = Array("Black", "Dark Red", "Red")

For j = 0 To 2
If ColorArray(2)(j) = sPat Then
iColorIndex = ColorArray(1)(j)
End If
Exit For
Next
Debug.Print iColorIndex

End Sub

--

Dave Peterson