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]

Warning, Air code...

sub ArrayFind(rngData as Range)
' rngData should be the range with data where you want to identify the Ys and Ns.

dim i as Integer
dim j as integer
dim arResult(1 to 10) as Integer
'presupposes that you know rngData will be 10 columns.  If it can change you might have to redim arResult with rngData.columns.count

j = 1
for i = 0 to 9 '(or Ubound of arResult - 1 / # Columns - 1)
if rngData.Offset(0,i) = "Y" Then arResult(j) = i
j = j + 1
next i

End Sub

This will result in an array like {1,3,7,9,0,0,0,0,0,0} since the array was set to be large enough to contain the results if all cells in the range = "Y." You could redim the array based on a count of the number of "Ys" although for a ten-element array I can't see the value in storage savings versus the cost of counting the Ys and redimming the array...

 

[Norie]

Chris

If you only want the array to have the columns with Y in it try this.

Dim rng As Range
Dim c As Range
Dim arr()
Dim I As Long
Set rng = Range("A1:J1")

For Each c In rng.Cells
     If c.Value ="Y" Then
        Redim Preserve arr(I)
        arr(I) = c.Column
        I=I+1
     End If
Next c

 

[dcardno]

actually, I like Norie's suggestion better anyways, but there is an error in the code I posted...

for i = 0 to 9 '(or Ubound of arResult - 1 / # Columns - 1) 
if rngData.Offset(0,i) = "Y" Then arResult(j) = i 
j = j + 1 
next i

should read:

for i = 0 to 9 'or to Ubound of arResult - 1 (which will be equal to # Columns - 1) 
if rngData.Offset(0,i) = "Y" Then
arResult(j) = i 
j = j + 1
end if
next i

 

[Chris The Rock]

THANKS very much.

 

[Chris The Rock]

Chris

If you only want the array to have the columns with Y in it try this.

Dim rng As Range
Dim c As Range
Dim arr()
Dim I As Long
Set rng = Range("A1:J1")

For Each c In rng.Cells
     If c.Value ="Y" Then
        Redim Preserve arr(I)
        arr(I) = c.Column
        I=I+1
     End If
Next c

When I step though this, the value for arr(I) changes to 'subscript out of range' at "I = I+1". I don't understand that...

 

[tusharm]

No need for VBA. Suppose your data are in A1:J1. Then, in some empty cell, say A4 enter the array formula (*)
=IF(COUNTIF($A$1:$J$1,"Y")<=ROW()-ROW($A$4),"",LARGE(($A$1:$J$1="Y")*(COLUMN($A$1:$J$1)),ROW()-ROW($A$4)+1))

Copy A4 down column A until you get a blank result.

(*) To complete an array formula, do *not* use the ENTER key. Instead use the SHIFT+CTRL+ENTER combination.

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.

 

[Chris The Rock]

Thanks for the reply, but this is a small part of a much larger VBA project that I'm working on, so I do need a VBA solution. I'm looking to set up an array of columns that will be used in executing the Subtotal method.

I have noted your solution, though. I might need it someday.

 

[Norie]

Chris

Do you happen to have this anywhere in the code?

Option Base 1

 

[Chris The Rock]

Chris

Do you happen to have this anywhere in the code?

Option Base 1

I don't.

Another question - How do I view the results? Forgive my Array-newbieness.