Menu
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 ?!?
364
asked Feb 24 '21 16:02
In VBA, to loop through an array you can use the For Loop (For Next). And to write this loop code you need to know the size of the array (upper and lower bound) so that you can use both as the counter for the loop. Basically, for loop will start from the first element of the array and loop up to the last.
This example uses the IsArray function to check if a variable is an array. Dim MyArray(1 To 5) As Integer, YourArray, MyCheck ' Declare array variables. YourArray = Array(1, 2, 3) ' Use Array function. MyCheck = IsArray(MyArray) ' Returns True.
One way to loop through a range is to use the For... Next loop with the Cells property. Using the Cells property, you can substitute the loop counter (or other variables or expressions) for the cell index numbers. In the following example, the variable counter is substituted for the row index.
Iterating over an array means accessing each element of array one by one. There may be many ways of iterating over an array in Java, below are some simple ways. Method 1: Using for loop: This is the simplest of all where we just have to use a for loop where a counter variable accesses each element one by one.
This tutorial will teach you how to loop through Arrays in VBA. For Each Loop – The For Each Loop will loop through each item in the array. For Next Loop – The For Next Loop will loop through specified start and end positions of the array (We can use the UBound and LBound Functions to loop through the entire array).
Iterating means going through elements one by one. As we deal with multi-dimensional arrays in numpy, we can do this using basic for loop of python. If we iterate on a 1-D array it will go through each element one by one. In a 2-D array it will go through all the rows. If we iterate on a n -D array it will go through n-1th dimension one by one.
When working with array formulas in Excel, you can observe how they calculate and store their items (internal arrays) to display the final result you see in a cell. To do this, select one or several arguments within a function's parentheses, and then press the F9 key. To exit the formula evaluation mode, press the Esc key.
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))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
