View Full Version : 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?
Blade Hunter
07-11-2010, 09:49 PM
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
Hope that helps
Dan
Edit: Quote: mikerickson: the resulting array's lowest index is 0.
I ALWAYS forget this :(. Code updated :) Thanks Mike
mikerickson
07-11-2010, 10:01 PM
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")
Dim arrayOfNumbers as Variant, i as Long
arrayOfNumbers = Array(1, 2, 4, 7, 10)
For i = 0 to 4
MsgBox arrayOfNumbers(i)
Next i
Another way would be. This explicitly sets the bounds of arrayOfNumbers. This arrayOfNumbers is "one based".
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
Arrays can be based on any number (including negative). This uses the ReDim statement to force arrayOfNumbers to have indices between 3 and 7.
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
To test if the ActiveCell is in any of the columns, I'd use code like
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
Aussiebear
07-12-2010, 12:17 AM
Ever thought about tutoring Mike?
mikerickson
07-12-2010, 12:31 AM
I've tutored enough to know that every math tutee will need help with logarithms.
To test if the ActiveCell is in any of the columns, I'd use code like
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
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?
mikerickson
07-12-2010, 12:47 PM
It would become
Dim arrayOfColumnNumbers
arrayOfColumnNumbers = Array(1, 2, 4, 7, 10)
If IsNumberic(Application.Match(ActiveCell.Column, arrayOfColumnNumers,0)) Then
' etc.
It would become
Dim arrayOfColumnNumbers
arrayOfColumnNumbers = Array(1, 2, 4, 7, 10)
If IsNumberic(Application.Match(ActiveCell.Column, arrayOfColumnNumers,0)) Then
' etc.
Thanks. I appreciate the help.
Opv
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.