Array Help!!!!

[DaveAMS]

i would suggest two arrays...one that contains the cell locations that you're wanting to read from, and a second to hold the values. something like the following:

<code>
option base 1
option explicit

dim ws as worksheet, aRows as variant, _
aValues(30) as variant, _
iCol as integer
iCol = 2 'column number that contains data for array
set ws = sheets("name")
aRows = array(5,8,11,14)

for i=1 to ubound(aRows)
aValues(i) = ws.cells(aRows(i),iCol)
next
</code>

I'm a little rusty on my syntax, and I haven't actually run this code...so apologies for any errors. If the columns change also, you should be able to make an array for those too.

[Salvatore]

DaveAMS, I really appreciate your help.

I am trying to understand arrays...in the code you gave..I noticed that the array called "aValues(30)" will have 30 elements...which of course I can change.

But I am confused about the 2nd array, I believe it is "aRows", you did not DIM it as an array, but you assigned it to be = to array(5,8,11,14)

Will that work?

I am going to try it...

Also since my columns will not change and they run from col 20 to col 30, how would I write that in the code?...Maybe iCol = 20 to 30???????????????

Not sure!

I really want to understand how to use arrays...I have been reading up on it in my VBA books & online, but for me to get the understanding I need to see code like yours broken down...

Thank You

[DaveAMS]

You've picked up on probably the most confusing part of visual basic arrays, Salvatore. There are actually two different types of arrays in vb: actual arrays, and variant arrays.

An actual array is declared with a dim varName() as varType declaration statement. You have a couple of options as far as array size: You can either declare it up front or you can make your array "dynamic," and use the redim statement to size it.

also, you should be aware of the "option base" keyword. In vb, array indeces normally start at 0. For instance, dim array(10) would give you an array with elements 0 through 9. Option base allows you to change this. option base 1 would make the prevous array go from 1 to 10.

You can also override this "on the fly," by using dim array(1 to 10).

As you pointed out, i have an array in my example below called aRows which isn't declared as an array. It's declared as a variant. This is a variant array (not to be confused with an array of variants).

As you are probably aware, a variant is a special "catch-all" variable type that exists in visual basic, that can hold just about anything: integers, floating points, strings, even objects. Well, it turns out that it can also hold an array. The array() function makes a regular variant into an array. the msdn website can probably give you some additional info on the array() function, as well as the topic of variant arrays.

hope this helps.

[cafeenman]

Originally posted by DaveAMS

also, you should be aware of the "option base" keyword. In vb, array indeces normally start at 0. For instance, dim array(10) would give you an array with elements 0 through 9.

Dave, this is wrong. If you declare that array it will have 11 elements - 0 through 10, not 0 through 9.

[DaveAMS]

Thanks for the correction, Cafeenman.

[Salvatore]

OK, I'm sorry guy's...but I am getting frustrated here ....

I need to use an If statment with the an array of rows that are not in order..i.e Row 5,8,11,18.....

The If statement will be within a loop to look through the array for a particular criteria, then it will change the Color Index of those cells and the ones Directly above & below that cell....

Example code:

Dim Mysheet As Object
Set Mysheet = Sheet2
Dim rwIndex As Integer
Dim colIndex As Integer
Dim rwIndex2 As Integer
rwIndex = 5
rwIndex2 = 4
For colIndex = 20 To 30

With Mysheet.Cells(rwIndex, colIndex)
 If .Value = "SLD" Then .Cells(rwIndex2, colIndex).Interior.ColorIndex = 8
 
 
        End With
    Next colIndex

This will do what I want , but just for that 1 row, and it will not highlight the rows above and below the cell with the True criteria....

So I am hoping that someone can steer me in the right direction..I understand the concept of arrays, but to put them together...well I just can not seem to get it

Thanx