CODEWITHSUNDEEP
arraysexcelvba
Aldo Sanchez
Aldo Sanchez

Reputation: 450

How can I add empty spaces between values to a vba array?

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:

(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,"",""]

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.

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

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?

Upvotes: 2

Views: 2467

Answers (5)

Aldo Sanchez
Aldo Sanchez

Reputation: 450

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

Upvotes: 0

brettdj
brettdj

Reputation: 55692

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")), ",,,"), ",")

Upvotes: 1

RubberDuck
RubberDuck

Reputation: 12758

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.

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

Upvotes: 1

PaulFrancis
PaulFrancis

Reputation: 5819

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

Upvotes: 5

Gary's Student
Gary's Student

Reputation: 96773

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

Upvotes: 2

Related Questions