在VBA或Excel中，如何遍历数组并在数组中包含值(如果数组包含值)的情况下填充一些单元格，而在数组中包含0的情况下填充0? [英] 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?

问题描述

我正在从Wix接收一个数组，其中包含用户通过表格选择的课程.

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

从Wix接收数组的示例，用户选择了2门课程.该数组放置在单元格中.

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

["VBA","Django"]

上面的数组放置在我的excel文件中的一个单元格中，从该位置我会对该单元格进行红色处理，并使用课程填充VBA数组:

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

但是用户可以选择1到10门课程.所有课程的数组:

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"]

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

我希望基于从Wix接收到的数组，将1放入数组中当前课程的单元格中，并将0放入其他课程中.

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.

例如，此课程选择了["VBA"，"Django"]，因此应在VBA单元格中放置1，在Django单元格中放置1，在重新编程的课程单元格中放置0，因为我想看看是什么用户选择的课程.

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 ?!?

推荐答案

不确定在VBA上的设置如何，但是可以根据动态Excel的版本来执行此操作，例如:

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

B2 中的公式:

Formula in B2:

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

---

按照@ T.M.他的宝贵意见是，您也可以在整个垂直范围内输入 FIND()中的第二个参数:

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

B2 中的公式:

Formula in B2:

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

这篇关于在VBA或Excel中，如何遍历数组并在数组中包含值(如果数组包含值)的情况下填充一些单元格，而在数组中包含0的情况下填充0?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！