Multidimensional array - lookup
 

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

Multidimensional array - lookup
 

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

Multidimensional array - lookup
 

Comment out Exit For in the For loop.

Regards,
p.

Multidimensional array - lookup
 

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

Multidimensional array - lookup
 

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

Multidimensional array - lookup
 

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