• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Error while doing Redim Preserve

rumshar

Member
Hi All,
My following code works fine but the array is static. I want to make it dynamic. When I tried doing it, it shows error as subscription out of range. Can you please let me know where I am going wrong?

>>> use code - tags <<<
Code:
Option Explicit

Public Sub Button1_Click()

Dim i As Integer
Dim strPath As String
Dim strArr(1 To 999, 1 To 1) As String '  I want to make it dynamic array
Dim strFile As String

'ReDim strArr(1 To 999, 1 To 1)

Cells(8, 3).ClearContents

strPath = Sheet1.Cells(4, 3).Value

If Right(strPath, 1) <> "\" Then
    strPath = strPath & "\"
End If

i = 1
strFile = Dir(strPath & "*")

Do While strFile <> vbNullString

'ReDim Preserve strArr(1 To i, 1 To 1)


strArr(i, 1) = strFile

strFile = Dir()


i = i + 1

Loop


Cells(8, 3).Resize(i) = strArr

End Sub
 
Last edited by a moderator:
rumshar
Please reread Forum Rules
Please post, new posts in the correct forums,
You opened this thread in Ask an Excel Question.
Isn't this
VBA Macros -question.
While You're rereading those,
You'l notice few more steps eg from
How to get the Best Results at Chandoo.org.
In this time, I'm moving this thread.
 
Using Redim with Preserve, you can only alter the upper boundary of the last dimension, not the first one as you are trying. You don't actually need a 2D array until you populate the cells, so use a 1D array prior to that (which you can directly use Redim Preserve with), then use Application.Transpose when populating the cells.
 
Back
Top