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

actually, i don't get an error. i just get unexpected results, i.e., the range "selected_rep_needs" does not get anything written to it. also, the array named "y" doesn't hold any values except for the last subscript (last value of "counter") — SteveS
– SteveS, Commented Sep 2, 2014 at 19:56
I don't suppose the range name argument in Application.Index that assigns values to x(i) should be "dev_needs_hdrs", not just "dev_needs"(?). — Matt Cremeens
– Matt Cremeens, Commented Sep 2, 2014 at 20:04
Also, it looks like you need ReDim Preserve or your array values will be overwritten. — Matt Cremeens
– Matt Cremeens, Commented Sep 2, 2014 at 20:07
thanks matt! actually "dev_needs_hdrs" is correct. i added that rather than redefining the "dev_needs" range to include the header row. probably bad programming. i didn't know about ReDim Preserve. I'm going to Google that now. — SteveS
– SteveS, Commented Sep 2, 2014 at 20:17

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

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related