Populating dynamic array


I have a small VBA subroutine, which aims to put some data from a worksheet into a two dimensional dynamic array. When compiled line-by-line (“step into”), it seems to work sometimes, that is, the data from the worksheet indeed gets into the array. However, when running by “Run Sub”, I get “Application-defined or object-defined error.” After such an error, even after stopping, and re-starting by “step into” leads to the same error again. (Nothing is changed on worksheet in the meanwhile.) Why is this happening? If my code is wrong, how come it works sometimes?


Sub CountYESArray()

Dim DataArray() As Variant, dataRows As Long, dataCols As Long

dataRows = Worksheets(“DS”).UsedRange.Rows.Count – 1
dataCols = Worksheets(“DS”).UsedRange.Columns.Count

ReDim DataArray(dataRows, dataCols)
DataArray = Worksheets(“DS”).Range(“A2”, Range(“c1”).End(xlDown)).Value

End Sub



By: Kinga

Leave a Reply

Your email address will not be published. Required fields are marked *