VBA array from table based on criteria

[ldarley]

Hi all,

I have a structured excel table where I need to pass a reference number into an array based on whether that reference is in use or not. The table is structured as follows:

ID Number	ListColumn(2)	ListColumn(3)	Status
1			In Use
2			In Use
3			Free
4			In Use
5 etc			Free

So in this example I would want to pass ID numbers 1,2 and 4 into the array based on them being 'In Use'. Any ideas on how I would go about the section of the code dealing with the array?

Many Thanks

 

[Fluff]

How about

Sub ldarley()
   Dim Ary As Variant
   
   With ActiveSheet.ListObjects("Table1")
      Ary = Filter(Evaluate("transpose(if(" & .ListColumns("Status").DataBodyRange.Address & "=""In Use""," & .ListColumns("ID Number").DataBodyRange.Address & ",false))"), False, False)
   End With
End Sub

 

[Peter_SSs]

Another possibility

Sub Into_Array()
  Dim Ary As Variant
 
  Ary = Application.Index(Range("Table1[ID Number]").Value, Filter(Application.Transpose(Evaluate(Replace("if(#=""In Use"",Row(#)-" & Range("Table1[#Headers]").Row & ",""x"")", "#", Range("Table1[Status]").Address))), "x", False), 1)
End Sub

There are a couple of slight differences between my results & Fluffs.
- If the ID numbers are in fact numerical like your sample, my code will populate the array with numbers whereas Fluff;s will populate with text.
- My array will be 1-based, Fluff's will be zero-based

 

[ldarley]

Thanks Peter and Fluff I've tried them out and they both work great, Peter I've leant towards your for the reason that the data is numerical.

Peter for my own learning exactly what is your code doing in the section related to the table headers is that because the index is looking at the whole column including the header and not just the databodyrange (or am I way off!)?

 

[Peter_SSs]

Peter for my own learning exactly what is your code doing in the section related to the table headers ...

The Evaluate part of my code (which is the part that uses [#Headers]) is to produce an array of row numbers for "In Use" rows. However, I want those row numbers to be relative to the DataBodyRange. In the example below the worksheet row numbers for the relevant rows are {4,5,7}. If we subtract the worksheet row number of the Headers row (3) that array becomes {1,2,4}

So the array is populated by indexing the [ID Numbers] column for rows {1,2,4} and column 1, feeding the required ID Numbers into the array.
Hope that makes sense.