VBA - Create an array from another array

Question

I would like to know how to do this, if possible. I have defined the arrayC(26) as:

Dim arrayC(26) As String
arrayC(26) = "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z"
Dim i As Integer 'Position in the arrayC(26)
i = 1

Based on the value of another cell, if it has a value of 3 I want to go from A to C. If it has a value of 5, I want to go from A to E. And so on.

Dim j As Integer
j = 1
ReDim arrayCont(j) As Variant

Let us assume it goes from A to C. So, I want that in arrayCont(j) I have the following values "A,B,C". Then I will go to Cells(8,"C") to check if it is equal to the value in the arrayCont(j). If so, I want to remove that value from the arrayCont(j). For instance, consider that Cells(8,"C") = "B". Then my arrayCont(j) will be "A,C". How can I do this?

The code I have created is:

Do While p <= sh1.Range("D6").Value
 For p = 1 To sh1.Cells(6, "D").Value
  If sh3.Cells(8, "C").Value = arrayC(p) Then
   arrayCont(j) = arrayCont(j)
  Else
   arrayCont(j) = arrayC(p)
  End If
 Next p
 j = j + 1
Loop

Thank you in advance.

Not an answer, but arrayC isn't what you think it is - it has empty strings for the first 26 entries (it starts at index 0) and then arrayC(26) is the full string of the alphabet. I believe you want Dim arrayC() As String and arrayC = Split("A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z", ",") — bobajob
– bobajob, Commented Dec 21, 2016 at 11:41
What does the arrayC = Split("A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z", ",") ? As you said, arrayC starts at index 0, but if I am defining i = 1 it starts with the first entry, right? — codeLearner
– codeLearner, Commented Dec 21, 2016 at 11:50
Try Debug.Print (arrayC(26)) with your code and Debug.Print (arrayC(25)) with my code to see the difference. — bobajob
– bobajob, Commented Dec 21, 2016 at 11:53
bobajob, I think both are correct since it doesn't anything and doesn't show any error. — codeLearner
– codeLearner, Commented Dec 21, 2016 at 12:43

Shai Rado · Accepted Answer · 2016-12-21 18:54:10Z

1

First, in order to add all the letters inside your brackets to elements in an array, the array need to be dynamic (Dim arrayC As...) , and not static (Dim arrayC(26) As...). Also, either you use Split, or you need to add " before and after each element inside your brackets.

Second, in order to find if the value in "C8" is found inside the copied array (arrayCont) , which is Redim up to i (=3), we use the Application.Match to find the index of the matching element inside the array. If there is a "match" then we remove that element from the array.

Third I am using a Sub called "DeleteElementAt" which removes a certain element from an array, depeneds on the index of the array you want to remove.

My Code (Tested)

Option Explicit

Sub SubArrayfromArray()

Dim arrayC As Variant
Dim i As Integer 'Position in the arrayC(26)
Dim arrayCont As Variant

' this is the way to put all the strings inside the brackets in an array (type Variant) in one line of code
arrayC = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z")
i = 3 ' i is the value of another worksheet cell

' copy original dynamic array to destination dynamic array
arrayCont = arrayC

' redim the copied array up to 3 (from 0 to 2)
ReDim Preserve arrayCont(0 To i - 1)

' value in Range C8  >> if "B" was found inside the arrayCont >> remove it from the array
If Not IsError(Application.Match(Range("C8").Value, arrayCont, 0)) Then
    Call DeleteElementAt(Application.Match(Range("C8").Value, arrayCont, 0) - 1, arrayCont)
End If
' for debug only
'For i = LBound(arrayCont) To UBound(arrayCont)
'    MsgBox "Array element " & i & " Value is " & arrayCont(i)
'Next i

' Edit 1: place arrayCont result in Cells(9, 3) >> Range("C9")
Range("C9").Resize(UBound(arrayCont) + 1, 1).Value = Application.Transpose(arrayCont)

End Sub

Sub DeleteElementAt Code

Public Sub DeleteElementAt(ByVal ArrIndex As Integer, ByRef myArr As Variant)

' this sub removes a certain element from the array, then it shrinks the size of the array by 1    
Dim i As Integer

' move all element back one position
For i = ArrIndex + 1 To UBound(myArr)
    myArr(i - 1) = myArr(i)
Next

' shrink the array by one, removing the last one
ReDim Preserve myArr(UBound(myArr) - 1)

End Sub

edited Dec 21, 2016 at 18:54

answered Dec 21, 2016 at 11:52

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

codeLearner Over a year ago

Shai Rado, I would like to print those values of arrayCont from Cells(9,3) to Cells(10,3) but it will do dynamically for other dimensions. The code I wrote: While p <= sh1.Range("D6").Value - 1 sh3.Cells(8 + i, 3) = arrayCont If sh3.Cells(8 + i, 3) = arrayCont Then Call DeleteElementArray(Application.Match(Cells(8 + i, 3).Value, arrayCont, 0) - 1, arrayCont) End If i = i + 1 p = p + 1 Wend The defined p is your i.

codeLearner Over a year ago

When it has to put letter C in cells(10,3) it returns run time error type 13. Can you help me please?

Shai Rado Over a year ago

i'm at home now, will try to look at it tonight, if not then tomorrow at work

Shai Rado Over a year ago

@vbalearner see my Edit 1 in my edited code (last 2 rows in "Sub SubArrayfromArray")

codeLearner Over a year ago

Thanks Shai Rado. It worked. I had to transpose the arrayCont to fill into a column

Collectives™ on Stack Overflow

VBA - Create an array from another array

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related