VBA - Need help with a concept - creating an array

Chris The Rock

Active Member
Joined
Feb 24, 2002
Messages
287
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.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
544
Office Version
  1. 2013
Platform
  1. Windows
Warning, Air code...
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
Chris

If you only want the array to have the columns with Y in it try this.
Code:
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

Well-known Member
Joined
Nov 20, 2002
Messages
544
Office Version
  1. 2013
Platform
  1. Windows
actually, I like Norie's suggestion better anyways, but there is an error in the code I posted...
Code:
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:
Code:
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
:oops:
 

Chris The Rock

Active Member
Joined
Feb 24, 2002
Messages
287
Chris

If you only want the array to have the columns with Y in it try this.
Code:
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,029

ADVERTISEMENT

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

Active Member
Joined
Feb 24, 2002
Messages
287
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
Chris

Do you happen to have this anywhere in the code?
Code:
Option Base 1
 

Forum statistics

Threads
1,141,679
Messages
5,707,787
Members
421,527
Latest member
Tamiwsw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top