How can I iterate over an array and populate some cells with 1 if the array contains the value, and 0 if not ,in VBA or Excel?

Question

I'm receiving from Wix an array with the courses selected by an user via a form.

Example of an array receive from Wix where an user selected 2 courses. This array is placed in a cell.

["VBA","Django"]

This above array is place inside a cell in my excel file from where I red the cell and populate a VBA array with the courses:

Dim data() As Variant
    Dim arrStr As String
    Dim dataItems As Long
    Dim i As Long
    Dim IndividualCourses(1 To 9) As String

    arrStr = Range("T3")
    arrStr = Replace(Replace(arrStr, "[", "{"), "]", "}")
    data = Application.Evaluate(arrStr)
    dataItems = Application.CountA(data)
    For i = 1 To dataItems
        IndividualCourses(i) = data(i)
    Next i

But the user can select between 1 and 10 courses. The array for all courses:

["JS","Python","VBA","Java","Spring","C++","C#",".NET","Django","CSS"]

I want, based on the array received from Wix, to put 1 in the cell of the courses present in the array and 0 in the other courses.

For example, this courses selected ["VBA","Django"], it should put 1 in the VBA cell and 1 in the Django cell, and 0 in the remianing courses cells, because I want to see what courses an user has selected.

Any ideea for this complicated problem ?!?

Answer 1

Not sure how set you are on VBA, but depending on your version of Excel you can do this with dynamic arrays, for example:

Formula in B2:

=--ISNUMBER(FIND(""""&B1:K1&"""",A2))

---

EDIT:

As per @T.M. his valuable comment you can feed the 2nd parameter in FIND() a whole vertical range too:

Formula in B2:

=--ISNUMBER(FIND(""&B1:K1&"",A2:A4))