Dealing with vba arrays

Question

This is my first question in this fórum. I am curious but pretty green to VBA and programming. I have been dealing with a problem for weeks, read lots of posts, but still feeling lost. Hope one of you can help!

Here is my code:

Private Sub CommandButton1_Click()
Dim numbers() As Double, size As Integer, i As Integer
Dim Numchart As Chart, y_st As Variant

size = WorksheetFunction.CountA(Worksheets(6).Columns(1))

ReDim Preserve numbers(size)

For i = 2 To size
If Cells(i, 2).Value = "PROD" Then
numbers(i) = Cells(i, 8).Value
End If
Next i

y_st = convert_arr(numbers())

Set Numchart = Charts.Add
Numchart.ChartType = xlLineMarkers
Numchart.SeriesCollection.NewSeries
Numchart.SeriesCollection(1).Values = y_st

End Sub


Public Function convert_arr(pass_arr() As Double) As String
For i = 0 To UBound(pass_arr())
    Select Case i
            Case 0
                convert_arr = "{" & pass_arr(0) & ","
            Case 1 To UBound(pass_arr()) - 1
               convert_arr = convert_arr & pass_arr(i) & ","
            Case UBound(pass_arr())
                convert_arr = convert_arr & pass_arr(i) & "}"
            End Select
Next
End Function`

With this code I get an non-continuous chart, because in between "PROD" there are other categories that I would like to filter out. Thank you advance,

Could You please define Your problem a little bit clearer? At least sample input and expected output? :} — Kamiccolo
– Kamiccolo, Commented Jun 17, 2014 at 15:05
I couldn't add pictures because of my low reputation. I was expecting a chart with only the PROD data defined in column 2, but the chart was displayind data where the categories were not PROD as zero. It is very difficult to describe without pictures — Elibas
– Elibas, Commented Jun 17, 2014 at 15:31

Rory · Accepted Answer · 2014-06-17 14:57:38Z

1

I suggest you restrict the array size using COUNTIF:

'additional variables

Dim ArraySize As Long
Dim counter As Long

Size = WorksheetFunction.CountA(Worksheets(6).Columns(1))
ArraySize = WorksheetFunction.CountIf(Worksheets(6).Columns(2), "PROD") - 1
ReDim Preserve numbers(ArraySize)

For i = 2 To Size
    If Cells(i, 2).Value = "PROD" Then
        numbers(counter) = Cells(i, 8).Value
        counter = counter + 1
    End If
Next i

answered Jun 17, 2014 at 14:57

Rory

34.2k5 gold badges40 silver badges42 bronze badges

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

Collectives™ on Stack Overflow

Dealing with vba arrays

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related