Dynamic Array not Resizing

[ScottyBee]

I have the following code:


Sub DynamicArray()
Dim numbers() As Integer, size As Integer, i As Integer
size = WorksheetFunction.CountA(Worksheets(1).Columns(1))
ReDim numbers(size)
For i = 1 To size
numbers(i) = Cells(i, 1).Value
Next i
MsgBox numbers(size)
End Sub


My worksheet looks like this and when I run my code, I keep getting "10" when the last value in my array is "20". Any ideas? Thanks26932

[p45cal]

This contains an unqulified reference (in red):
numbers(i) = Cells(i, 1).Value
If the active sheet is not the same sheet as Worksheets(1) then you're likely to get incorrect results (this assumes that the code is sitting in a standard code module and not a sheet's code module - if it's in a sheet's code module then an unqulaified reference refers to that sheet's cells, so make sure the code is in an apppropriate module)
So in the first instance, you could try changing just that line to:
numbers(i) = Worksheets(1).Cells(i, 1).Value

With so many possibilities where it may go wrong, much better to attach a sample file.

[Paul_Hossler]

Well, FWIW ... I get 8 as an answer, but P45cal could have the issue


I keep getting "10" when the last value in my array is "20". Any ideas? Thanks

I don't see any 20 in the data


26934





Option Explicit


Sub DynamicArray()
Dim numbers() As Integer, size As Integer, i As Integer

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

MsgBox size

ReDim numbers(size)

MsgBox LBound(numbers) & " -- " & UBound(numbers)

For i = 1 To size
numbers(i) = Cells(i, 1).Value
Next i

MsgBox numbers(size)
End Sub

[ScottyBee]

P45Cal, your suggestion was spot on! I had my array on sheet2. But as you said, my code only worked when Sheet2 was the active sheet. By qualifying the reference to


For i = 1 To size
numbers(i) = Worksheets(2).Cells(i, 1).Value
Next i


The code ran just fine on the other sheets. Also thanks to Paul as I learned some new methods to use with arrays. :)