Array element not displaying all the data rows

TotallyConfused

Board Regular
Joined
May 4, 2017
Messages
246
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
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 50136John10
Row 60145John9
Row70134John8
Row 8015John7
Row 90132John6
Row 100131John5
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
 
Last edited:

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,367
Office Version
  1. 365
Platform
  1. Windows
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
Rich (BB code):
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
VBA Code:
For Ary2rows = 1 To [B]UBound(Ary2, 1)[/B]
     MsgBox "Ary2(Ary2rows, 2) = " & Ary2(Ary2rows, 2)
Next
 

TotallyConfused

Board Regular
Joined
May 4, 2017
Messages
246
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,367
Office Version
  1. 365
Platform
  1. Windows
Your problem is that you are not loading the arrays with the data that you think you are loading.

Example
VBA Code:
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

Rich (BB code):
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

Board Regular
Joined
May 4, 2017
Messages
246
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,367
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the kind words. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,126,999
Messages
5,622,114
Members
415,876
Latest member
csibonga2k17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top