VBA Populate range from array

Question

I created an array which is populated if a condition is met - so far so good. Now the values which met the condition need to be copied to a different sheet.

Actually I have to questions: 1. I can't get the values in the range in the first place.; 2. The array stores 4 columns (range) for each row if the criteria is met. Would it be possible if I can determine each array column and paste it in a specific column (in the array the columns are next to each other, but in the destination sheet they are not).

This the code I have so far:

Sub determineDelta()
'Start determination and copy values to Delta sheet
Worksheets("Source").Activate
Range("A2").Select
numberOfRecords = Range(Selection, Selection.End(xlDown)).Rows.Count + 1

Dim myArray() As Variant
ReDim myArray(1 To 500) As Variant
Dim i, j, k As Integer
k = 0
ReDim myArray(numberOfRecords, k) As Variant

    For i = 2 To numberOfRecords
        If IsError(Application.Match(Cells(i, "A").Value, Sheets("SE16N").Range("A:A"), 0)) Then
            For j = 2 To 6
                myArray(j, k) = Cells(i, j).Value
                Debug.Print myArray(j, k)
            Next j
            k = k + 1
            ReDim Preserve myArray(numberOfRecords, k)
        End If
    Next i

Worksheets("Delta").Activate
Range("I2:I" & UBound(myArray)) = "FI"
Range("J2:J" & UBound(myArray)) = "A"
Range("M2").Resize(UBound(myArray), 1).Value = Application.Transpose(myArray)
End Sub

I've been looking on the internet for 2 days, including cpearson. In this article about array sizing (on the bottom) it states #N/A. This is what I do have! But don't want. :-)

I have the feeling I'm not that far off and wouldn't be surprised if it is just something relatively small.

I hope someone can help me.

newacc2240 · Accepted Answer · 2017-10-11 16:54:49Z

Some advice added, not sure if this is what you need.

Sub determineDelta()
    'Start determination and copy values to Delta sheet
    Worksheets("Source").Activate
    Range("A2").Select
    numberofrecords = Range(Selection, Selection.End(xlDown)).Rows.count + 1

    Dim myArray() As Variant
    'Unnecessary code
    'ReDim myArray(1 To 500) As Variant
    Dim i, j, k As Integer
    k = 0
    'ReDim myArray(numberOfRecords, k) As Variant
    ReDim myArray(numberofrecords, 2 To 6) As Variant

    For i = 2 To numberofrecords
        '[1]
        If Not IsError(Application.Match(Cells(i, "A").Value, Sheets("SE16N").Range("A:A"), 0)) Then
            For j = 2 To 6
                '[2]
                'myArray(j, k) = Cells(i, j).Value
                'Debug.Print myArray(j, k)
                myArray(k, j) = Cells(i, j).Value
            Next j
            k = k + 1
            'Since we already have a large enough array, no need to redim here
            'ReDim Preserve myArray(numberofrecords, k)
        End If
    Next i

    With Worksheets("Delta")
        .Range("I2:I" & UBound(myArray)) = "FI"
        .Range("J2:J" & UBound(myArray)) = "A"
        '[3]
        '.Range("M2").Resize(UBound(myArray), 1).Value = Application.Transpose(myArray)
        .Range("M2").Resize(UBound(myArray), 5).Value = myArray
    End With
End Sub

[1]: IsError will return TRUE if there is an error in the following formula. If Cells(i, "A") is found in the Sheets("SE16N"), it won't go into the for loop. So myArray will always catch values that are not in Sheets("SE16N"). After adding a NOT operator, only items could be found are able to load into myArray. However, if you need to catch the values that are not shown in Sheets("SE16N"), the origin is right.

[2]: It's a waste that you claimed an array about hundred rows but you access only row #2 to #6. I changed (j, k) into (k, j), so hopefully most of the array can be used.

[3]: Since I exchanged the row and column, the array no longer needs to be transposed. In your original code, resize the range will get you a range with several rows and only one column. So only the first column of the array can be copied into the worksheet, feel free to change 5 back to 1 if I'm wrong.

If you need to paste the data into separated columns, two methods come up in my mind. 1. You can store the data into different arrays at the beginning. 2. Loop through the array several times such as:

Set OriginCell = Range("M2")
Set OriginCell2 = Range("Q2")
For i = 0 to UBound(myArray)
    OriginCell.Offset(i).Value = myArray(i, 2)
    OriginCell2.Offset(i).Value = myArray(i, 3)
Next i

Thanks a lot. When I change the above code with your comments, it works.

Dennis · Accepted Answer · 2017-10-12 12:53:25Z

Thanks a lot. When I change the above code with your comments, it works. [1] Yes, if the value doesn't exist it needs to do something. So I didn't change that part. [2] I guess I still need to read more about arrays, because this is not 100% clear to me. [3] Awesome. Didn't know it was possible.

Yes, the columns in the array need to go to individual columns. I tried it with your code, but it didn't do the trick. I get the point what you're trying to do. However, if I insert it like this, it will use the current sheet, i.e. Source, while the data needs to be in sheet Delta.

When setting the ranges I included the Worksheets function as well and it works like charm:

Set OriginCell = Worksheets("Delta).Range("M2")
Set OriginCell2 = Worksheets("Delta).Range("Q2")
For i = 0 to UBound(myArray)
    OriginCell.Offset(i).Value = myArray(i, 2)
    OriginCell2.Offset(i).Value = myArray(i, 3)
Next i

There are only some empty rows in the array, but I will be able to manage that myself. :) Thanks!

Collectives™ on Stack Overflow

VBA Populate range from array

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related