Array element not displaying all the data rows

[TotallyConfused]

Sub LoadColsIntoArray()
      ' This code worked perfect with no problem EXCEPT as noted in
      ' the TEST SECTION at the end.
   Dim ws2 As Worksheet: Set ws2 = Sheets("Sheet2")
   Dim Ary2 As Variant
   Dim Ary2rows As Long
   Dim Cols As String
   Dim LastRow As Long

Application.ScreenUpdating = False

With ws2
     Cols = "6,20"               ' Only load columns 'F' and 'T' into Ary2 array
     LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row
     Ary2 = Application.Index(Cells, Evaluate("ROW(5:" & LastRow & ")"), Split(Cols, ",")) ' Data starts in row #5
End With
    
' TEST SECTION:
       ' Okay, now to test whether everything was actually loaded into Ary2
Ary2rows = 1
       ' To confirm column 'F' was loaded into array, I added the following three lines. Everything worked perfect.
   For Ary2rows = 1 To UBound(Ary2, 1)
         MsgBox "Ary2(Ary2rows, 1) = " & Ary2(Ary2rows, 1)
   Next
        ' To confirm column 'T' was loaded into array, I added the following three lines. Encountered a problem.
        ' The MsgBox displays ONLY 'John10' & 'John9' and nothing else. There were NO error messages.
        ' It's as if there were ONLY those two names in the column when loaded into Ary2.
        '--- What is wrong that is causing this problem? ---
   For Ary2rows = 1 To UBound(Ary2, 2)
         MsgBox "Ary2(Ary2rows, 2) = " & Ary2(Ary2rows, 2)
   Next
         ' Out of curiosity to see what would happen, I added the following three lines. This time, I used the value of
         ' UBound forAry2 column 1 as the loop counter, but then display values in column #2.
         ' In this case everything worked perfect with all 6 John's being displayed. This tells me that Column 'T' was
         ' completely loaded
    For Ary2rows = 1 To UBound(Ary2, 1)                              ' NOTE: I'm accessing element #1 of the array, but
         MsgBox "Ary2(Ary2rows, 2) = " & Ary2(Ary2rows, 2)  '    displaying values from element #2
    Next
         ' The following code displays the number 6 as it should
               MsgBox "Ubound(Ary2,1) = " & UBound(Ary2, 1)
          ' The following code displays the number '2' for column 'T', but it should be the number '6'
                MsgBox "Ubound(Ary2,2) = " & UBound(Ary2, 2)
    Application.ScreenUpdating = True
End Sub

Column F Column T

Row 1
Row 2
Row 3
Row 4
Row 5	0136		John10
Row 6	0145		John9
Row7	0134		John8
Row 8	015		John7
Row 9	0132		John6
Row 10	0131		John5

If anyone has any idea why Ary2 column 2, will only display the first two names, yet all the rest of that column's data seems to be there, I'd sure love to hear your thoughts. There is no other code in this workbook other than what you see here.

THANK YOU in advance for any help you may be able to offer.
TotallyConfused

 

[Peter_SSs]

UBound(Ary2, 2) is not how many elements are in the second column of the array, it is how many columns are in the array - that is 2 in your case.

Therefore in this code your counter is going from 1 to 2 but it is being used in the row section of the array. Hence you only get 2 values

For Ary2rows = 1 To UBound(Ary2, 2)
         MsgBox "Ary2(Ary2rows, 2) = " & Ary2(Ary2rows, 2)
   Next

The correct way to access all 6 rows from column 2 of the array is what you did do next

For Ary2rows = 1 To [B]UBound(Ary2, 1)[/B]
     MsgBox "Ary2(Ary2rows, 2) = " & Ary2(Ary2rows, 2)
Next

 

[TotallyConfused]

Sub LoadColsIntoArray()

   Dim ws1 As Worksheet: Set ws1 = Sheets("Sheet1")
   Dim ws2 As Worksheet: Set ws2 = Sheets("Sheet2")
   
   Dim Ary1 As Variant
   Dim Ary2 As Variant
   
   Dim Cols As String
   Dim LastRow As Long
   Dim Ary1rows As Long
   Dim Ary2rows As Long
   
Application.ScreenUpdating = False
   
With ws1
        Cols = "1,6"                                      '  Columns 'A' and 'F'
        LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row
        Ary1 = Application.Index(Cells, Evaluate("ROW(5:" & LastRow & ")"), Split(Cols, ","))     ' Data starts in row #5
End With

With ws2
        Cols = "6,20"                                       ' Columns 'F' and 'T'
        LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row
        Ary2 = Application.Index(Cells, Evaluate("ROW(5:" & LastRow & ")"), Split(Cols, ","))     ' Data starts in row #5
  End With

Ary1rows = 1
Ary2rows = 1
      ' NOTE: When the above code is run and the cursor is in Sheet 1 then
             ' MsgBox Ary1(Ary1rows, 1) = displays the answer of 000001 which is correct
             ' MsgBox Ary2(Ary2rows, 1) = displays the answer of 'F 5'. I have NO idea why?
       ' When the above code is run and the cursor is in Sheet 2 then
             ' MsgBox Ary1(Ary1rows, 1) = displays a blank but should show the answer of 000001
             ' MsgBox Ary2(Ary2rows, 1) = displays the answer of 000006 which is correct
       ' It seems the cursor has to be in the same sheet as the Array was created from in order to get the correct answer. I have no
       ' idea why, because I assumed the MsgBox command was accessing the ARRAY and NOT the sheet.
   Exit Sub
      ' NOTE:  I haven't done much work with the following two loops because I kept having so many problems, I decided to go
      ' back up in my code and try to find where things were going wrong. That's the reason for the 'Exit Sub' command above here,
      ' to stop execution of the code until my problem is solved. I think the loops are close to being accurate, but not sure.
      ' There may be a better, more direct way, rather than these loops.
      
' For Ary1rows = 1 To UBound(Ary1, 1)
    For Ary2rows = 1 To UBound(Ary2, 1)
         If (Ary2(Ary2rows, 2) = Ary1(Ary1rows, 1)) Then
              Ary1(Ary1rows, 2).Value = Ary2(Ary2rows, 2).Value
         Else
              MsgBox "not found"
              MsgBox "Ary1(Ary1rows, 1) = " & Ary1(Ary1rows, 1)    ' gives nothing if cursor on sheet 2 - seems to access current sheet?
              MsgBox "Ary2(Ary2rows, 1) = " & Ary2(Ary2rows, 1)
         End If
     Next
' Next
   Application.ScreenUpdating = True
End Sub

' THANK YOU Peter for your quick response. I do know what that last number means when dealing with arrays. Why I referred to it as
' an 'Element', I have no idea. I want to apologize to you for my incorrect selection of words to describe what was happening.
'
' I'd like to give a description of my program, the processes I'm coding at the moment and what it will need to do when finished.
' To avoid any more confusion of terminology, I'll refer to the Excel sheets, since what I'm trying to do with the arrays, I'd be doing with Excel.
' There are two sheets, Sheet1, whose columns 'A' & 'F' were loaded into Ary1. Sheet2, whose columns 'F' & 'T' were loaded into Ary2.
' The data on both sheets start at row 5.
' The contents of Sheet2, column 'F' and Sheet1, column 'A' will be the same, though they may NOT be in the same order.
'
' The purpose of this program, is to scan down Sheet2, column 'F' picking up each cell's value. Then scan down Sheet1, column 'A' until
' finding that same value. When a match is found, then copy Sheet2, column 'T' from that same row, and paste it into the same row on
' Sheet1, column 'F'. The original contents of Sheet1, column 'F' will now be overwritten.
'
' In terms of working with the two arrays, this means that each row in Ary2, column 1 will be scanned, and the corresponding value from
' Ary1, column 1 will be found. When a match is made, then the value from Ary2, column 2 will be pasted into Ary1, column 2. The end
' result of all this is that Ary2, column 2 will now be in Ary1, column 2. When this copy/paste process is completed, then I would transfer
' Ary1, column 2 back into Sheet 1, column 'F'.
'
' Layout and contents of Sheet1......... Layout and contents of Sheet2
' All data starts in row 5......................... All data starts in row 5
' ............. A ...................F.........................................F......................T
' 5........000001 ......... F 5..............................000006.............John10
' 6........000002.......... F 6..............................000005............,John9
' 7........000003...........F 7..............................000004.............John8
' 8........000004...........F 8..............................000003.............John7
' 9........000005...........F 9..............................000002.............John6
'10.......000006...........F 10...........................000001.............John5
'
' I'm sorry I haven't used MrExcel's program of XL2BB to display the data from my two sheets, but I've tried downloading and carefully following
' the exact instructions three times, but I still can't get it to be part of Excel.
'
' I hope the information provided above will be of some help. Any help or advice you may offer will be greatly appreciated. Thank you in advance.
'
' TotallyConfused

 

[Peter_SSs]

Your problem is that you are not loading the arrays with the data that you think you are loading.

Example

With ws1
        Cols = "1,6"                                      '  Columns 'A' and 'F'
        LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row
        Ary1 = Application.Index(Cells, Evaluate("ROW(5:" & LastRow & ")"), Split(Cols, ","))     ' Data starts in row #5
End With

Although this says "With ws1" it will find LastRow using the active sheet and also fill the array with values from the active sheet, not necessarily ws1. If you want it to use ws1 for those 2 task you need to qualify Cells/Ranges to ws1 with a "." as follows

With ws1
        Cols = "1,6"                                      '  Columns 'A' and 'F'
        LastRow = .Cells.Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row
        Ary1 = Application.Index(.Cells, Evaluate("ROW(5:" & LastRow & ")"), Split(Cols, ","))     ' Data starts in row #5
End With

 

[TotallyConfused]

Hello Peter

You Sir, are a genius and I can't THANK YOU enough for your help with this program. I'd spent a great deal of time and effort trying to figure out why I kept getting random results. For some reason I never spotted those two missing periods. You have a sharp eye to have noticed that problem. I like that WITH...END WITH command and have used it a lot. I may make other mistakes, but I'm quite sure, thanks to you, I'll be sure to use periods where needed next time. With your help, I was able to finish this program and post it here on MrExcel. Hopefully what I wrote will be of assistance to another user.

THANKS again Peter for your help. Keep up the good work.

TotallyConfused

 

[Peter_SSs]

You're welcome. Thanks for the kind words.