Creating a Simple Array

Try as I may I still can't grasp the concept of creating an array. What is the syntax for creating a simple array with a group of column numbers, say, columns 1, 2, 4, 7, 10, so that I could check against the array to determine if the activecell.column is within the array?

[vba]
Sub temp()
Dim Arr() As Variant
Dim InArray As Boolean
Arr = Array(1, 2, 4, 7, 10)
InArray = False
For X = LBound(Arr) To UBound(Arr)
If ActiveCell.Column = Arr(X) Then InArray = True
Next
If InArray Then MsgBox "Found in Array"
End Sub
[/vba]

Hope that helps

Dan

Edit: Quote: mikerickson: the resulting array's lowest index is 0.

I ALWAYS forget this :(. Code updated :) Thanks Mike

This is one way. Note that when using the function Array, the resulting array's lowest index is 0. (This is called a "zero based array")
[VBA]Dim arrayOfNumbers as Variant, i as Long

arrayOfNumbers = Array(1, 2, 4, 7, 10)

For i = 0 to 4
MsgBox arrayOfNumbers(i)
Next i[/VBA]
Another way would be. This explicitly sets the bounds of arrayOfNumbers. This arrayOfNumbers is "one based".
[VBA]Dim arrayOfNumbers(1 to 5) As Long, i as Long

arrayOfNumbers(1) = 1
arrayOfNumbers(2) = 2
arrayOfNumbers(3) = 4
arrayOfNumbers(4) = 7
arrayOfNumbers(5) = 10

For i = 1 to 5
MsgBox arrayOfNumbers(i)
Next i[/VBA]
Arrays can be based on any number (including negative). This uses the ReDim statement to force arrayOfNumbers to have indices between 3 and 7.
[VBA]Dim arrayOfNumbers() As Long, i as Long

ReDim arrayOfNumbers(3 to 7)

arrayOfNumbers(3) = 1
arrayOfNumbers(4) = 2
arrayOfNumbers(5) = 4
arrayOfNumbers(6) = 7
arrayOfNumbers(7) = 10

For i = 3 to 7
MsgBox arrayOfNumbers(i)
Next i[/VBA]
To test if the ActiveCell is in any of the columns, I'd use code like
[VBA] If IsNumeric(Application.Match(ActiveCell.Column, Array(1, 2, 4, 7, 10), 0)) Then
MsgBox Active Cell.Address & " is in one of the columns."
Else
MsgBox ActiveCell.Address & " is not."
End If[/VBA]

Ever thought about tutoring Mike?

I've tutored enough to know that every math tutee will need help with logarithms.

Quote:

---

Originally Posted by mikerickson

To test if the ActiveCell is in any of the columns, I'd use code like
[vba] If IsNumeric(Application.Match(ActiveCell.Column, Array(1, 2, 4, 7, 10), 0)) Then
MsgBox Active Cell.Address & " is in one of the columns."
Else
MsgBox ActiveCell.Address & " is not."
End If[/vba]

---

Thanks to everyone for the help. It makes sense. I like the above suggestion for testing whether the active cell is in one of the defined columns. How would the above script be revised to accommodate the array being defined outside the Match statement, for example, if an array called dateCols has already been created and defined prior to getting to the Match test?

It would become
[VBA]Dim arrayOfColumnNumbers
arrayOfColumnNumbers = Array(1, 2, 4, 7, 10)

If IsNumberic(Application.Match(ActiveCell.Column, arrayOfColumnNumers,0)) Then
' etc.[/VBA]

Quote:

---

Originally Posted by mikerickson

It would become
[vba]Dim arrayOfColumnNumbers
arrayOfColumnNumbers = Array(1, 2, 4, 7, 10)

If IsNumberic(Application.Match(ActiveCell.Column, arrayOfColumnNumers,0)) Then
' etc.[/vba]

---

Thanks. I appreciate the help.

Opv