CODEWITHSUNDEEP
excelvba
Elibas
Elibas

Reputation: 3

Dealing with vba arrays

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,

Upvotes: 0

Views: 107

Answers (1)

Rory
Rory

Reputation: 34035

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

Upvotes: 1

Related Questions