Build Array in Excel VBA
Desired result: The result would be a function that builds an array of values from column B. The values are restricted by having the same value in column A. 'E.g. Column A value = 1 myArray = (0,1,2)' 'Column B Value = 2 myArray = (4,5,6,7,8)'
First time trying to use arrays in VBA and need help. I have the following data in columns A and B in Excel:
A B 1 0 1 1 1 2 2 4 2 5 2 6 2 7 2 8 3 9 3 10 3 11 4 12 4 15 4 18
I have the following VBA Code:
Function buildMyArray() Dim ARange as Range Dim B as Integer Dim myArrary as Variant For Each ARange In Sheets("SheetName").Range("B:B") If ARange.Value = 1 Then B = Application.WorksheetFunction.VLookup(ARange.Value, Sheets("SheetName").Range("A:B"), 2, False) myArray = Array(B) End If Next ARange End Function
I am trying to build a function that will search column A for each instance of an integer (which I will eventually dynamically pass). Then for each instance of that integer do a vlookup on the adjacent column (B). I then want to create an array of the values from the lookup (in the above case (0,1,2)).
The above code is the closest I have gotten. I get returned the first value in B (0) as the only value in the array. I am reasonably confident this is because the array logic is in the If statement. However, if I move it out of there I get compile errors.
Please help a novice learn. Thanks.
think of an array as one row of an excel sheet, with the columns being the indexes. What you want to do you said was store values in said array. So you get your value, b, and you want to put that in the array.
what you want to do:
when declaring the array, declare it like Dim myArrary() as Variant notice the brackets (not positive if they are required but its best practise.
that declares an empty array. Like an excel sheet with no columns. if you knew for A fact the array would have 5 values, you could declare:
Dim myArrary(1 to 5) as Variant that says, its an array with 5 indexes. (like an excel row with 5 columns)
the numbers, 1 to 5, those are the start and end index numbers. so, in that array, when reffering to the first spot, you'd put myArray(1) the brackets like that are how you refer to specific indexes in the array. second spot would be myArray(2) 3rd myArray(3) 4th myArray(4) and 5th (the last spot) would be myArray(5).
You can declare themn like dim myarray(0 to 5) as variant, that means the first index would be myArray(0) - this array would have 6 spot - 0,1,2,3,4,5
So, in your code, you'd need a counter to keep track what iteration of the loop you were on and increment it like counter = cojunter + 1 at the bottom of the loop. (it would increase each time the loop ran) then, replace the line that says myArray = Array(B) with myArray(counter) = B
so each time the loop runs, B will go into a spot in the array.
1 more thing,
because we dont know at the begginning how many spots the array will have, and we declare it empty: dim myArray() as variant you must redefine it after to have spaces before we can add anything. so, at the beginning of the loop, you wanna put Redim Preserve MyArray(1 to counter) this redefines your array as being from 1 to whatever your counter is at. since the counter increases by 1 each time, 1 space will be added to the array each time.
replace Dim myArrary as Variant with Dim myArrary() as Variant add: dim counter as long up in the variables (name it whatever u want, just be consistant)
add counter = 1 before the loop to initialize the counter
add ReDim myArray(1 to counter) in the loop, in the if statment (thus only adding the space if B was found
add counter = counter + 1 at the end if the if, but again inside the is, thus only incrementing if a value for B is found
and change myArray = Array(B) to MyArray(counter) = B in the if statement.
Here is a sample that I created. Try this
Option Explicit Sub sample() Dim ws As Worksheet Dim rng As Range, aCell As Range, bCell As Range Dim MyAr() As String Dim n As Long Dim SearchString '~~> Set this to the relevant worksheet Set ws = ThisWorkbook.Sheets("Sheet1") SearchString = 2 With ws '~~> Set this to your range Set rng = .Range("A1:A14") Set aCell = rng.Find(What:=SearchString, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If Not aCell Is Nothing Then Set bCell = aCell '~~> Store the value from Col B in the array ReDim Preserve MyAr(n) MyAr(n) = aCell.Offset(, 1).Value n = n + 1 '~~> Find Next occurance Do Set aCell = rng.FindNext(After:=aCell) If Not aCell Is Nothing Then If aCell.Address = bCell.Address Then Exit Do '~~> Store the value from Col B in the array ReDim Preserve MyAr(n) MyAr(n) = aCell.Offset(, 1).Value n = n + 1 Else Exit Do End If Loop End If End With '~~> This will give you the results For n = LBound(MyAr) To UBound(MyAr) Debug.Print MyAr(n) Next n End Sub
In VBA, there's no simple concept of "adding an item to an array". You always have to declare beforehand how big you want the array to be, then you can work with the items in the array. While it's possible to write code that increases the size of an array by one element, this usually isn't the correct approach.
In this scenario, building the array will be a two-step process: first you need to count how many items you will be putting in your array, then you need to populate the array. In my example, x is the number you're searching for in column A.
Function MakeArray(x As Integer) As Variant Dim result() As Variant Dim result_count As Long Dim i As Long Dim cursor As Range result_count = WorksheetFunction.CountIf(Range("A:A"), x) ' size the array to how many elements we need, minus one ' because the array indexes start at zero ReDim result(result_count - 1) ' scan through the data and populate our array Set cursor = Range("A2:B2") i = 0 Do Until IsEmpty(cursor(1)) If cursor(1) = x Then result(i) = cursor(2) i = i + 1 If i > UBound(result) Then Exit Do End If Set cursor = cursor.Offset(1) Loop MakeArray = result End Function
Might well not count as an answer, but without VBA, in C2 and copied down to suit:
then copy ColumnC and Paste Special Values over the top, filter to select does not contain ) and delete visible should show:
ColumnA start of range ColumnB end of range ColumnC array