Array with 2 Dimensions

[jagrenet]

Hello Gurus,

I am working with an Array(arr1) that is comparing a known or "static" list of filenames against the contents of a specific Folder. A loop runs through the list of filenames and checks the folder to verify if the file is there or not.
Dimension "1" of the Array is the filename ("i"). - Sheet1.Range("E11:E22")
Dimension "2" of the Array is the result of the comparison ("J"). - Uses Offset to indicate if file is there. It puts a "1" in the corresponding row of Column F

Everything is working beautifully - VERY well.
Now, what I would like to do, ..... since "J" already returns the count of "missing" files correctly, I want to take each missing file and create another Array (arr2), and have it return the filename to arr2, rather that the .Value of "J" which is simply 1 or "" (Empty).
And this is where I am getting stuck.

I have tried a few different code functions but still only get a numeric from "J".


Dim arr1 As Variant
arr1 = Sheet1.Range("E11").CurrentRegion 'Pass REQUIRED FILE List to Array

Dim arr2 As Variant

Dim i As Long, J As Variant

For i = LBound(arr1, 1) To UBound(arr1, 1) 'Enumerate(Index) "NAME" of file (Column "E")
For J = LBound(arr1, 2) To UBound(arr1, 2) 'Enumerate "COUNT" of file, if it exists in the folder (Column "F")

Debug.Print i, J, arr1(i, J)

Next J
Next i

Sheet1.Range("F25").Formula = "=SUM(F11:F22)"
tResult = Sheet1.Range("F25")

For Each J In arr1
If arr1(J) = "" Then
arr2 = i
Exit For
End If
Next
What am I missing ??

Thanks in advance,
Jeff

 

[HaHoBe]

Hi Jeff,

maybe

Public Sub MrE_1232987_1705009()
' https://www.mrexcel.com/board/threads/array-with-2-dimensions.1232987/
Dim arr1 As Variant
Dim arr2 As Variant
Dim i As Long, j As Long

arr1 = sheet1.Range("E11").CurrentRegion 'Pass REQUIRED FILE List to Array
ReDim arr2(1 To 1)

For i = LBound(arr1, 1) To UBound(arr1, 1) 'Enumerate(Index) "NAME" of file (Column "E")
  If arr1(i, 2) = 1 Then
    j = j + 1
    ReDim Preserve arr2(1 To j)
    arr2(j) = arr1(i, 1)
  End If
Next i

For j = LBound(arr2) To UBound(arr2)
  Debug.Print j, arr2(j)
Next i

End Sub

Ciao,
Holger

 

[jagrenet]

Hi Jeff,

maybe

Public Sub MrE_1232987_1705009()
' https://www.mrexcel.com/board/threads/array-with-2-dimensions.1232987/
Dim arr1 As Variant
Dim arr2 As Variant
Dim i As Long, j As Long

arr1 = sheet1.Range("E11").CurrentRegion 'Pass REQUIRED FILE List to Array
ReDim arr2(1 To 1)

For i = LBound(arr1, 1) To UBound(arr1, 1) 'Enumerate(Index) "NAME" of file (Column "E")
  If arr1(i, 2) = 1 Then
    j = j + 1
    ReDim Preserve arr2(1 To j)
    arr2(j) = arr1(i, 1)
  End If
Next i

For j = LBound(arr2) To UBound(arr2)
  Debug.Print j, arr2(j)
Next i

End Sub

Ciao,
Holger

Hi Holger,

Yes, ..... that is very close to what I am needing however, just the inverse. - "j" represents the files that are missing and those are the files I wish to reference by filename. "1" means the file exists. Blank means file is missing. I am trying to get the names of files missing. The Column on the right is Column F which is the result of "j". From my original post above - (Dimension "2" of the Array is the result of the comparison ("J"). - Uses Offset to indicate if file is there. It puts a "1" in the corresponding row of Column F). I hope this makes sense. I think you are on the right track ....... just need to "invert" ReDim somehow.

 

[HaHoBe]

Hi Jeff,

looking at the code the Next for the second loop should not be for i but for j (my very bad habit of changing variables and not testing).

From what I understand just change

  If arr1(i, 2) = 1 Then

to either

  If arr1(i, 2) = "" Then

or

  If arr1(i, 2) = vbNullString Then

Ciao,
Holger

 

[jagrenet]

Hi Jeff,

looking at the code the Next for the second loop should not be for i but for j (my very bad habit of changing variables and not testing).

From what I understand just change

  If arr1(i, 2) = 1 Then

to either

  If arr1(i, 2) = "" Then

or

  If arr1(i, 2) = vbNullString Then

Ciao,
Holger

Hi Holger,

Brilliant !!! - I should have been able to catch that myself but, -

If arr1(i, 2) = "" Then worked perfectly !!

Thank you so much for taking the time to work with this. I appreciate your help !!
Have a great day,
Jeff