Excel VBA Array

Question

This is making me nuts. I am trying to extract the values of columns that fit my criteria (TRUE or 1) from a user-selected row , save only those to an array, and then write the array to a range. That's all. Can anyone tell me what I'm doing wrong?

Sub DevNeeds()
Dim x(), y(), needs() As Variant
Dim counter As Integer

columns_in_range = Range("dev_needs_hdrs").Columns.Count
counter = 1

Debug.Print "i", "counter", "y(counter)"

For i = 1 To columns_in_range
    ReDim x(columns_in_range), needs(columns_in_range)
    x(i) = Application.Index(Range("dev_needs"), Range("selected_row").Value, i)
    needs(i) = Application.Index(Range("dev_needs_hdrs"), 1, i)

    If (x(i) = True Or x(i) = 1) Then
        ReDim y(counter)
        y(counter) = needs(i)
        counter = counter + 1
    End If
Next i
counter = counter - 1

With Range("selected_rep_needs")
    .ClearContents
    .Resize(1, counter) = y
End With

End Sub

SteveS · Accepted Answer · 2014-09-02 22:28:52Z

2

Also, it looks like you need ReDim Preserve or your array values will be overwritten. – Matt Cremeens

THIS WAS THE PROBLEM. Thanks Matt. Wish I could give you credit for the answer, but you posted it as a comment! -- SteveS.

answered Sep 2, 2014 at 22:28

SteveS

331 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

StorymasterQ Over a year ago

You should also accept your own answer, as this will encourage other people to look into this question and learn as well :)

SteveS Over a year ago

I will do that, as soon as I'm allowed to. It's a 3-day wait to OK your own answer. Thanks for looking at the thread, though. I appreciate the quick help I got on here!

Collectives™ on Stack Overflow

Excel VBA Array

1 Answer 1

2 Comments

Your Answer

Collectives™ on Stack Overflow

Excel VBA Array

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest