How to populate an array with results?

[LiamO]

Hi,

Can anyone help me with the following?

(Please see the attached screenshot ...)

I want to create and populate 5 arrays, the names of which include the contents of cells A4 to A8. (i.e. Front Office Array, Back Office Array, etc)

If I take the "Front Office Array" (Cell A4) as an example, I want to check the contents of cells B4 to P4 and where a cell in this range contains an "X" I want to add the corresponding value in row 2 of the same column to the array. (i.e. Front Office Array = ("XYZ", "DEF", "NKF", "JUI", "DSA", "REW", "FDS", "QWA", "POP"))

I also want then to make the arrays and their contents available to other functions.

Any help you can provide is really appreciated.

Thanks.

[Bob Phillips]

How would the array be structured. Would it have gaps where the value is not X, or would it be that the matched values would be contiguous?

[LiamO]

It would be the latter, i.e. matched values would be contiguous.
Using the example in the screenshot I have attached, the arrays should be populated as follows:
Front Office Array = ("XYZ", "DEF", "NKF", "JUI", "DSA", "REW", "FDS", "QWA", "POP")
Back Office Array = ("DEF")
Configuration Array = ("GHT", "DSA")
Quality Array = ()
Customer Care Agent Array = ("XYZ", "LOP", "PLI", "QWA", "POP")

[Bob Phillips]

Then I think you would need to loop each row looking for X and grab the value in row 1 if so. Just simple looping.

[LiamO]

I'm fairly new to VBA ... would you be able to help me out here with an example of how to do this?

Is it possible to define the arrays based on the values in column A (cells A4:A8)?
When I have the arrays defined and populated how do I make them available to other functions?

Thanks.

[snb]

sub M_snb()
sn=filter([if(B4:P4="X",B1:P1,"_")],"_",false)
end Sub

[LiamO]

I ran this code but I get the following error ... 'Run-time error '13': Type mismatch'

I have to admit I don't fully understand what the output of this function should be ... maybe you can elaborate?

What I want is a piece of code that can define an array based on the content of a cell in column A and populate the array based on content in columns B to P. Is this possible?

As an example (see screenshot in initial post):
- cell A4 contains a string 'Front Office'
- using code create an array called 'Front Office Array'
- populate the array by looping through B4 to P4 .... i.e. if cell B4 = 'X' then add the value of cell B2 (i.e. "XYZ") to the array, if cell B4 does not contain "X" then do not add anything to the array and skip to the next column
- the 'Front Office Array' should end up with the following contents ... ("XYZ", "DEF", "NKF", "JUI", "DSA", "REW", "FDS", "QWA", "POP")
- repeat the same procedure for cells A5, A6, A7 and A8 ... so I end up with 5 arrays
- make all 5 arrays available to other functions

[snb]

I amended the previous code.
the result is an array: sn

[Kenneth Hobs]

There is no way, in VBA, to dynamically create a variable or array name.

Try another method such as an array where the first element is the name of that row of data. I can show you that if needed.

Another method would be to use a Collection or Dictionary Object since you don't have duplicates.

[LiamO]

Thanks all for your help so far ...

Yes Kenneth if you can show me some examples of these methods I would appreciate it.

[Kenneth Hobs]

I used a method similar to snb's. My method does not require hard coding of the ranges. Use debug or debug.print or MsgBox() to see what parts like the variable s resolve to when played. It is not all that complicated. I broke up parts so you can see each step.

Once played from that sheet, a() will be available to other routines.

The jagged array method:
Option Explicit

Public a() As Variant

Sub FillPublicArrayA()
Dim nRows As Long, i As Long, lastCol As Long, j As Integer
Dim c As Range, r As Range, rr As Range
Dim x As Variant, xx As Variant, s As String, q As String

q = """"

Set rr = Cells(1, Columns.Count).End(xlToLeft)
lastCol = rr.Column
Set r = Range("A3", Range("A" & Rows.Count).End(xlUp))
nRows = r.Rows.Count
ReDim a(1 To nRows) As Variant

i = 0
For Each c In r
i = i + 1
Set rr = Range("B" & c.Row, Cells(c.Row, lastCol))
s = "If(" & rr.Address & "=" & q & "X" & q & "," & _
Range("B1", Cells(1, Columns.Count).End(xlToLeft)).Address & ",""_"")"
x = Filter(Evaluate(s), "_", False)
ReDim xx(0 To UBound(x) + 1)
For j = 0 To UBound(x) + 1
If j = 0 Then
xx(0) = c.Value
Else
xx(j) = x(j - 1)
End If
Next j
a(i) = xx
MsgBox Join(a(i), vbLf)
Next c
End Sub
I most always use Option Explicit. For snb's routine:
Sub snb()
Dim x As Variant
x = Filter([if(B4:P4="X",B1:P1,"_")], "_", False)
MsgBox Join(x, vbLf)
End Sub

[LiamO]

Hi Kenneth,

I ran this code but it is exiting at line 'x = Filter(Evaluate(s), "_", False)' with the error message: 'Run-time error '13': Type mismatch'.

Using watch I can see that the value of s at this point is: "If($B$4="X",$B$2:$BA$2,"_")"

Any thoughts?

[Kenneth Hobs]

From that, you should note that the problem is:
Set r = Range("A3", Range("A" & Rows.Count).End(xlUp))

This is probably because you did not play the macro from the workbook and sheet that has date in A3 and down.

For a range like r:
Debug.Print r.Address
After setting r will show the address of $B$4 wherein lies the problem.

[snb]

see the attachment, containing:

Sub snb()
MsgBox Join(Filter([if(B4:P4="X",B1:P1,"_")], "_", False), vbLf)
End Sub

[Kenneth Hobs]

I tinkered with a Collection method but a Class method might be the better method.

Attached is an example using the method that I posted earlier. It does not address filters.

[snb]

to loop through the rows:

Sub M_snb()
For Each cl In Columns(1).SpecialCells(2)
If cl.Row > 1 Then
cl.Offset(, 1).Resize(, 15).Name = "snb_003"
MsgBox cl.Value & vbLf & Join(Filter([if(snb_003="X",B1:P1,"_")], "_", False), vbLf)
End If
Next
End Sub

[mperrah]

I changed the row to "2" and the msg box displays the names of the applications instead of 0.
You could add a count for the values returned as well if you need totals.
I guess it doesnt matter what the array is named if the results are usable :)

Sub M_snb()
For Each cl In Columns(1).SpecialCells(2)
If cl.Row > 1 Then
cl.Offset(, 1).Resize(, 16).Name = "snb_003"
MsgBox cl.Value & vbLf & Join(Filter([if(snb_003="X",B2:P2,"_")], "_", False), vbLf)
End If
Next
End Sub