CODEWITHSUNDEEP
arraysexceldynamicvba
codeLearner
codeLearner

Reputation: 86

VBA - Create an array from another array

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.

Upvotes: 0

Views: 5531

Answers (1)

Shai Rado
Shai Rado

Reputation: 33682

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

Upvotes: 1

Related Questions