Subscript our of range with ReDim Preserve and UBound

[prinzip]

Hello.

I partly created a code which tries to group data based on the values of an array. The problems are that I'm trying to extract data from multiple worksheets that belong to the same workbook so ReDim Preserve seems to fall out of range when the code reaches the 2nd worksheet. I'm not sure what to do in order to fix it. Change something specific or use another method than ReDim Preserve?

here is the code:

Sub Scatter_data()
   Dim ws As Worksheet, Row As Long, LastRow As Long 
   Dim BaseValue() As Double, SelectedValue() As Double, 
   Dim k As Long, n As Long, x As Long, i as Long

   Application.ScreenUpdating = False

   For Each ws In Worksheets
      
       LastRow = ws.Range("C2").End(xlDown).Row

      n = 1
        For i = 2 To LastRow
        For k = 750 To 3500 Step 250
            If ws.Range("J" & i).Value > k - k * 0.01 And ws.Range("J" & i).Value < k + k * 0.01 Then
                ReDim Preserve BaseValue(n)
                BaseValue(n) = ws.Range("J" & i).Value
                n = n + 1
            End If
        Next k
        Next i
        
        ReDim Preserve SelectedValue(UBound(BaseValue), 3)
        
      x = 1
        For i = 2 To LastRow
        For k = 750 To 3500 Step 250
            If ws.Range("J" & i).Value > k - k * 0.01 And ws.Range("J" & i).Value < k + k * 0.01 Then
                SelectedValue(x, 1) = ws.Range("J" & i).Value
                SelectedValue(x, 2) = ws.Range("N" & i).Value
                SelectedValue(x, 3) = ws.Range("AR" & i).Value
                x = x + 1
            End If
        Next k
        Next i

   Next
   
   Application.ScreenUpdating = True
   
End Sub

 

[RoryA]

You can only resize the last dimension if you use the Preserve option.

 

[prinzip]

You can only resize the last dimension if you use the Preserve option.

Yes, that's why I use the same routine twice. Firstly, in order to determine how many values the array should include and secondly in order to enrich with the values.

 

[RoryA]

I think you missed my point - you can't do this:

Redim preserve selectedvalue(ubound(basevalue), 3)

because it's resizing the first dimension. As it happens you don't need Preserve there since the array is empty.

 

[prinzip]

I think you missed my point - you can't do this:

Redim preserve selectedvalue(ubound(basevalue), 3)

because it's resizing the first dimension. As it happens you don't need Preserve there since the array is empty.

Took a while but got it. Rearranged geometry a bit. Any other ideas rather than ReDim Preserve? Seems to me that this command has a lot of disadvantages.

            For k = 750 To 3500 Step 250
                If ws.Range("J" & i).Value > k - k * 0.01 And ws.Range("J" & i).Value < k + k * 0.01 Then
                    ReDim Preserve BaseValue(n)
                    ReDim Preserve SelectedValue1(n)
                    ReDim Preserve SelectedValue2(n)
                    BaseValue(n) = ws.Range("J" & i).Value
                    SelectedValue1(n) = ws.Range("N" & i).Value
                    SelectedValue2(n) = ws.Range("AR" & i).Value
                    n = n + 1
                End If
            Next k
        Next i
    Next ws

 

[nuked]

How about a collection instead of an array?