No Loop through all items in an array assistance

[johnnyL]

I found a line of code that goes through all items in arrays without looping. The line of code writes the result to a range in a worksheet.

I would like to know if there is a way to write the results, separated by commas, to a string.

I would like three strings in total. One string = to the contents of ArrayA, second string = contents of ArrayB, and a third string = to the results currently displayed to a worksheet.

I can do all of that with loops, but I was wondering if there is a way to change the no loop line of code to save to the strings that I mentioned.

Here is the code example that shows the looping version as well as the one line of code that doesn't loop:

Sub NoLoopThroughArrayValues()

    Dim ArrayCount  As Long
    Dim I           As Long
    Dim ArrayA      As Variant
    Dim ArrayB      As Variant
'
    ArrayA = Array(1, 2, 3, 4, 5)
    ArrayB = Array(2, 4, 6, 8, 10)
    ArrayCount = UBound(ArrayA) + 1
'
'
' Loop version for arrays
    For I = 1 To ArrayCount
        Range("D" & I) = ArrayA(I - 1) * ArrayB(I - 1)
    Next
'
'
' Or No Loop version for arrays equivalent
    Range("E1").Resize(ArrayCount) = Evaluate("{" & Join(ArrayA, ";") & "} * {" & Join(ArrayB, ";") & "}")
'
End Sub

 

[Rick Rothstein]

Not entirely sure what you are asking for but maybe this will help...

CommaDelimitedText = Join(ArrayA, ",")

 

[Akuini]

I would like to know if there is a way to write the results, separated by commas, to a string.

Perhaps :

MsgBox Join(Application.Transpose(Evaluate("{" & Join(ArrayA, ";") & "} * {" & Join(ArrayB, ";") & "}")), ",")

 

[Rick Rothstein]

@Akuini... why are you using Evaluate? Unless I totally misunderstood, ArrayA and ArrayB are one-dimensional arrays... Join will work on them directly (like I show in Message #2 for ArrayA).

 

[Akuini]

@Rick Rothstein
Hm, I think what OP wants as the result is arrayA multiplied by arrayB. So I just used his code:

Range("E1").Resize(ArrayCount) = Evaluate("{" & Join(ArrayA, ";") & "} * {" & Join(ArrayB, ";") & "}")

and put the result to string not to a range.
But I might be wrong.

 

[Rick Rothstein]

@Rick Rothstein
Hm, I think what OP wants as the result is arrayA multiplied by arrayB.

In looking back over his posting, I think you are right. I totally missed the asterisk (multiplier symbol).

 

[Marc L]

I would like three strings in total

According to Excel basics :​

Sub Demo1()
  Const C = ","
    Dim S$, T$, R$
        S = Join([COLUMN(A:E)], C)
        T = Join([COLUMN(A:E)*2], C)
        R = Join(Evaluate("{" & S & "}*{" & T & "}"), C)
        MsgBox S & vbLf & vbLf & T & vbLf & vbLf & R
End Sub

 

[johnnyL]

Not entirely sure what you are asking for but maybe this will help...

CommaDelimitedText = Join(ArrayA, ",")

That worked very well @Rick Rothstein. Thank you so much!

Perhaps :

MsgBox Join(Application.Transpose(Evaluate("{" & Join(ArrayA, ";") & "} * {" & Join(ArrayB, ";") & "}")), ",")

That worked for the resultstring @Akuini. Thank you so much!

Hm, I think what OP wants as the result is arrayA multiplied by arrayB. So I just used his code:

Correct, there was a multiplication being done on the two arrays.

Current code:

Sub NoLoopThroughArrayValues()

    Dim ArrayCount          As Long
    Dim I                   As Long
    Dim ArrayAString        As String
    Dim ArrayBString        As String
    Dim ArraysResultString  As String
    Dim ArrayA              As Variant
    Dim ArrayB              As Variant
'
    ArrayA = Array(1, 2, 3, 4, 5)
    ArrayB = Array(2, 4, 6, 8, 10)
    ArrayCount = UBound(ArrayA) + 1
'
'
' Loop version for arrays
    For I = 1 To ArrayCount
        Range("D" & I) = ArrayA(I - 1) * ArrayB(I - 1)
    Next
'
'
' Or No Loop version for arrays equivalent
    Range("E1").Resize(ArrayCount) = Evaluate("{" & Join(ArrayA, ";") & "} * {" & Join(ArrayB, ";") & "}")
'
'
    ArrayAString = Join(ArrayA, ",")                                                                            ' Save all items in ArrayA to a string
    ArrayBString = Join(ArrayB, ",")                                                                            ' Save all items in ArrayB to a string
    ArraysResultString = Join(Application.Transpose(Evaluate("{" & Join(ArrayA, ";") & "} * {" & Join(ArrayB, ";") & "}")), ",")    ' Save ResultArray to string
'
Debug.Print "Items in ArrayAString = " & ArrayAString
Debug.Print "Items in ArrayBString = " & ArrayBString
Debug.Print "Items in ArraysResultString = " & ArraysResultString
'
End Sub

 

[Marc L]

​

Transpose is useless, see post #7 …​

 

[johnnyL]

According to Excel basics :​

Sub Demo1()
  Const C = ","
    Dim S$, T$, R$
        S = Join([COLUMN(A:E)], C)
        T = Join([COLUMN(A:E)*2], C)
        R = Join(Evaluate("{" & S & "}*{" & T & "}"), C)
        MsgBox S & vbLf & vbLf & T & vbLf & vbLf & R
End Sub

@Marc L I haven't tested your code but it appears to make an assumption that should not be made. That is partially my fault though. I made up the values in each array just to provide some values. The actual values will be unknown until they are actually put into the arrays.

Example could have been:
ArrayA = Array(1, 12, 34, 8, 11)
ArrayB = Array(7, 5, 2, 9, 6)