如何在值到vba数组之间添加空格？

I create an array in vba by looping through cells in a sheet (originalWS). So let's say cells (2,5) to (2,12) have the following:

我通过循环遍历表单中的单元格(originalWS)在vba中创建一个数组。所以我们假设单元格(2,5)到(2,12)具有以下内容:

(2,5)Item
(3,5)Type 
(4,5)Nominal Diameter
(5,5)Lead
.
.
.
(12,5)For Use with End Blocks

Thus, when I loop with the code below I get an array that looks like this:

因此,当我循环使用下面的代码时,我得到一个如下所示的数组:

[Item,Type,Nominal Diameter,Lead,...,For Use with End Blocks].

[项目,类型,公称直径,铅,......,用于端块]。

However, I would like to add two empty spaces between each value in my array. so that it looks like this:

但是,我想在我的数组中的每个值之间添加两个空格。所以它看起来像这样:

[Item,"","",Type,"","",Nominal Diameter,"","",Lead,"","",...,For Use with End Blocks,"",""]

[Item,“”,“”,Type,“”,“”,Nominal Diameter,“”,“”,Lead,“”,“”,......,用于结束块,“”,“”]

ReDim propertyArr(1, lastRow)
For i = 1 To lastRow
    propertyArr(1, i) = originalWS.Cells(i + 1, 5).Value
    Debug.Print propertyArr(1, i)
Next

I have tried to loop to by the final total size of the array so (lastRow*3) and step forward by 3. However, I'm having a hard time figuring out how I would reset my orginalWS.cells(i,5) values so that they are consecutive.

我试图循环到数组的最终总大小,所以(lastRow * 3)并向前走3.然而,我很难弄清楚如何重置我的orginalWS.cells(i,5)这些值是连续的。

In other words, when I loop stepping by 3 my values would be:

换句话说,当我循环步进3时,我的值将是:

propertyArr(1,1) = originalWS.Cells(2,5).value
propertyArr(1,4) = originalWS.cells(5,5).value
propertyArr(1,7) = originalWS.cells(8,5).value

How can I loop so that I store values in my array every 2 places, while I get the values from a consecutive list in a sheet.

我如何循环以便每两个位置将值存储在我的数组中,而我从一个表中的连续列表中获取值。

Can I do this without having to add extra empty row a way to add two empty spaces between each value within my original loop without having to add extra empty rows?

我是否可以这样做而无需添加额外的空行,以便在原始循环中的每个值之间添加两个空格而不必添加额外的空行?

Or, can I add the two empty spaces between each value after I created my array the first time?

或者,我可以在第一次创建数组后在每个值之间添加两个空格?

5 个解决方案

#1

This should do the trick,

这应该是诀窍,

Dim lRowNo As Long

lRowNo = lastRow * 3
ReDim propertyArr(1, lRowNo)

For i = 1 To lRowNo 
    If i Mod 3 = 1 Then
        propertyArr(1, i) = originalWS.Cells(i + 1, 5).Value
    Else
        propertyArr(1, i) = ""
    End If
    Debug.Print propertyArr(1, i)
Next

#2

Something like:

Sub ytrewq()
    Dim propertyArr(), lastRow As Long
    Dim originalWS As Worksheet
    Set originalWS = ActiveSheet
    lastRow = 5
    ReDim propertyArr(1, 2 * lastRow)
    For i = 1 To 2 * lastRow Step 2
        propertyArr(1, i) = originalWS.Cells(i + 1, 5).Value
        propertyArr(1, i + 1) = ""
        Debug.Print propertyArr(1, i)
    Next
End Sub

UNTESTED

#3

You can also unroll the loop a bit to do this a bit more efficiently. Note that for each iteration, i isn't incremented by 1, but by 3.

您也可以稍微展开循环以更有效地执行此操作。请注意,对于每次迭代,i不会递增1,而是递增3。

Public Sub test()

    Dim lastRow As Long
    lastRow = 6

    Dim lastIndex As Long
    lastIndex = lastRow * 3

    ReDim propertyArr(1 To lastIndex)

    Dim i As Long
    For i = 1 To lastIndex Step 3
        propertyArr(i) = CInt(i / 3)
        propertyArr(i + 1) = vbNullString
        propertyArr(i + 2) = vbNullString
    Next


End Sub

#4

Or without loops

或者没有循环

Dim ws As Worksheet
Set ws = Sheets(1)
propertyarr = Join(Application.Transpose(ws.Range("E1:E5")), ","""","""",")

to put back into array

放回阵列

propertyarr = Split(Join(Application.Transpose(ws.Range("E1:E5")), ",,,"), ",")

#5

I figured out the answer. I wasn't updating the cells I needed correctly. See code below:

我想出了答案。我没有正确更新我需要的细胞。见下面的代码:

count = 3
lastIndex = lastRow * 3

ReDim propertyArr(1, lastIndex)
For i = 1 To lastIndex Step 3
        propertyArr(1, i) = originalWS.Cells((count - 1), 5)
        count = count + 1
Next

#1