VBA - Need help with a concept - creating an array

[Chris The Rock]

In my code, I'm looping through a range of cells that's one row deep and 10 columns wide. The possible values in the cells are "Y" and "N".

What I want to do is create an array whose results are the column numbers of the cells in that range whose values are "Y".

For example, Values Y, N, Y, Y, N, N, N, N, Y, Y would yield the results: (1,3,4,9,10)

I'm having a tough time figuring out where to start. Can anyone help?

Thanks in advance.

 

[dcardno]

Norie - the "Option Base 1" issue would cause a problem with the first "Y" encountered, since at that point I = 0 so we are trying to access the 0th entry in a 1-based array.

Chris - the arr(I) value will report a subscript out of range becasue arr() was just redimmed to arr(I), and then I was incremented by one (ready for the next turn through the loop when a "Y" is encountered). This is nt an issue, though, except when you are trying to test the values when stepping through the code. If you never encounter another "Y" in the data range, then the new I value will be ignored; if you do then arr() will be redimmed to the new I value (ie - the old Ubound of the array, plus 1) and the coresponding column value will be saved in the Ith position in the array.

The array itself will be returned (if the sub is accessed as a function) or will be created and available to a subsequent process - in any event this portion of the routine will always end with "I" equal to the "Ubound of arr() + 1" - but presumably you are not using "I" to access the values in the array.

 

[Chris The Rock]

Norie - the "Option Base 1" issue would cause a problem with the first "Y" encountered, since at that point I = 0 so we are trying to access the 0th entry in a 1-based array.

Chris - the arr(I) value will report a subscript out of range becasue arr() was just redimmed to arr(I), and then I was incremented by one (ready for the next turn through the loop when a "Y" is encountered). This is nt an issue, though, except when you are trying to test the values when stepping through the code. If you never encounter another "Y" in the data range, then the new I value will be ignored; if you do then arr() will be redimmed to the new I value (ie - the old Ubound of the array, plus 1) and the coresponding column value will be saved in the Ith position in the array.

The array itself will be returned (if the sub is accessed as a function) or will be created and available to a subsequent process - in any event this portion of the routine will always end with "I" equal to the "Ubound of arr() + 1" - but presumably you are not using "I" to access the values in the array.

Perfect explanation, thanks. I was just starting to figure this out, and this cleared everything up.