Array Problem

[danane]

Hello,
I always get the following error-message:"Subscript out of range."
Please help me finding the mistake in the code. Thanks!!


Dim z As Integer
Dim Arr() As String
Dim intcounter As Integer


With Application.FileSearch
.NewSearch
.LookIn = "C:\Lists"
.FileType = msoFileTypeAllFiles
.Execute

For z = 1 To .FoundFiles.Count
If Left(.FoundFiles(z), 3) = "txt" Then
Arr(intcounter) = .FoundFiles(z)
intcounter = intcounter + 1
ReDim Preserve Arr(intcounter)
End If


Next z

End With

Sheets("navigation").ListBox1.List = Arr

 

[Chance1234]

which line are you getting the error ?

Chance,

Filmmaker, taken gentleman and He tan e epi tas

 

[SkipVought]

hi,

...
    ReDim Preserve Arr(z-1)
    Arr(z-1) = .FoundFiles(z)
...

Skip,

_{[red]Be Advised![/red] A chicken, who would cross the road for 2 cents, is…
Poultry in motion for a paltry amount!}

 

[rmikesmith]

You declare Arr as a dynamic array but do not ReDim before accessing it the first time. Rearrange your code to the following:

For z = 1 To .FoundFiles.Count
   If Left(.FoundFiles(z), 3) = "txt" Then
     intcounter = intcounter + 1
     ReDim Preserve Arr(intcounter)
     Arr(intcounter) = .FoundFiles(z)
   End If
Next z

If Option Base is 0 (the default) then you need to account for the subscript being off by one, which is what Skip is getting at.

You could also handle this with an Option Base 1 statement at the top of your code module or change the ReDim statement:

ReDim Preserve Arr(1 to intcounter)

Regards,
Mike

 

[danane]

Thanks for your replies. It still says:"Subscript out of range." at line "Sheets("navigation").ListBox1.List = Arr".
I replaced that line with "Debug.Print Arr(intercounter)" and it still won't work.

 

[GlennUK]

Do you mean intercounter or intcounter? Make sure your spelling is OK.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[danane]

I corrected the code to the following:

Dim z As Integer
Dim Arr() As String
Dim intcounter As Integer

With Application.FileSearch
.NewSearch
.LookIn = "C:\List"
.FileType = msoFileTypeAllFiles
.Execute

For z = 1 To .FoundFiles.Count
If Left(.FoundFiles(z), 3) = "txt" Then
intcounter = intcounter + 1
ReDim Preserve Arr(1 To intcounter)
Arr(intcounter) = .FoundFiles(z)
Debug.Print Arr(intcounter)
End If

Next z

End With

End Sub

There is no Error-Message anymore, but the debug-field stays empty, even though I've got several txt-files under C:\List...

Any other suggestions?

 

[GlennUK]

Why are you doing:

Left(.FoundFiles(z), 3)

to get the last 3 chars of a string?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[rmikesmith]

As Glenn hinted, you are using the wrong function. Substitute Right$() and your code works for me.


Regards,
Mike

 

[danane]

Great! It works now! Thanks a lot for your help!