CODEWITHSUNDEEP
arraysexcelvba
Amatya
Amatya

Reputation: 1243

Excel VBA combining variable with array to create new Array of Strings

I have an array of Dates. In my code, I extract the array of dates from a column in a sheet which has date data, drop the unique values using scripting dictionary.

arrayIDAll = WorksheetFunction.Transpose(Sheets(2).Range(Cells(2, 3), Cells(Rows.Count, 3).End(xlUp)).Value)



Dim dc As Object

Set dc = CreateObject("Scripting.Dictionary")


For i = LBound(arrayDateAll) To UBound(arrayDateAll)
   If Not dc.Exists(arrayDateAll(i)) Then
      dc.Add arrayDateAll(i), i
   End If
      If Not dcID.Exists(arrayIDAll(i)) Then
      dcID.Add arrayIDAll(i), i
   End If
Next i

Array1 = dc.Keys()

So lets say that Array1 was like below:

 Array1 = Array(1/1/2012, 6/1/2012, 1/1/2013)

I have two string variables

Name1 = "Weight"
Name2 = "Cholesterol"

I want to create an array of Strings that looks like this:

   ArrayDateNames= Array("1/1/2012_Weight","6/1/2012_Weight","1/1/2013_Weight","1/1/2012_Cholesterol","6/1/2012_Cholesterol","1/1/2013_Cholesterol")

The code I am trying is not working

Sub combinearray()

      Dim arr As Variant, arr2 As Variant



      arr = Array("1 / 1 / 2012", "6 / 1 / 2012", "1 / 1 / 2013")

      ReDim Preserve arr(1 To 3)

      Name1 = "Weight"

      Name2 = "Cholesterol"

      ReDim arr2(1 To 2 * UBound(arr))

      For i = 1 To 2 * UBound(arr)

            If (i < 4) Then
                  arr2(i) = CStr(arr(i)) & "_" & Name1
            Else
                  arr2(i) = CStr(arr(i - 3)) & "_" & Name2
            End If

            Debug.Print arr2(i)
      Next i




End Sub

The debugger is giving me stuff like:

1 / 1 / 2012_Weight
6 / 1 / 2012_Weight
1 / 1 / 2013_Weight
1 / 1 / 2012_Cholesterol
6 / 1 / 2012_Cholesterol
1 / 1 / 2013_Cholesterol
2.98210735586481E-03Weight
4.96770988574267E-04Weight
4.97017892644135E-04Cholesterol
2.98210735586481E-03Cholesterol
4.96770988574267E-04Cholesterol

 2.98210735586481E-03 
 4.96770988574267E-04

Also, in this MWE I am entering dates in quotes but my actual array has date data. I am concerned that that might mess up the string concatenation.

Upvotes: 0

Views: 558

Answers (1)

Bernard Saucier
Bernard Saucier

Reputation: 2270

On my system, your code executes perfectly with the debugger output : 

1 / 1 / 2012_Weight
6 / 1 / 2012_Weight
1 / 1 / 2013_Weight
1 / 1 / 2012_Cholesterol
6 / 1 / 2012_Cholesterol
1 / 1 / 2013_Cholesterol

Maybe if you try : For i = 1 To UBound(arr2) instead of For i = 1 To 2 * UBound(arr)

Upvotes: 1

Related Questions