CODEWITHSUNDEEP
arraysexcelvba
SteveS
SteveS

Reputation: 33

Excel VBA Array

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

Upvotes: 0

Views: 364

Answers (1)

SteveS
SteveS

Reputation: 33

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.

Upvotes: 2

Related Questions