VBA: Array/Ubound sometimes working - not always returning full array

Question

I previously had a question answered for something unrelated (looping) and the recommendation included using arrays & Ubounds which all worked great at first (see my accepting the answer) but now the array portion isn't returning a full array ALL the time when activating or calling this sub.

Background

I have a worksheet(s) where 2 columns Range("D7:E28") contain user input/selections that are then sent to a hidden "Lookup" ws where a table is located for a cross ref/match. The resulting lookup results are then sent to a "Summary List" ws and listed one after the other.

Link: VBA: Worksheet loop only staying on the active sheet instead of looping through all specified worksheets in the workbook

Code:

Sub WS_Lookup()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim LookupWS As Worksheet
    Dim SummaryCodeWS As Worksheet
    Dim lookupLR As Long
    Dim summaryRow As Range
    Dim arr As Variant
    
    Set wb = ActiveWorkbook
    Set LookupWS = wb.Worksheets("Lookup")
    Set SummaryCodeWS = wb.Worksheets("Summary List")
    lookupLR = LookupWS.Range("K10000").End(xlUp).Row
                
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    For Each ws In wb.Worksheets
        If ws.Visible = xlSheetVisible And ws.Name <> " " Then
                    
            Set summaryRow = SummaryCodeWS.Range("B10000").End(xlUp).Offset(2).EntireRow
            summaryRow.Columns("A").Value = ws.Range("A4").Value
                    
            LookupWS.Range("G3:H24").Value = ws.Range("D7:E28").Value
                    
            arr = LookupWS.Range("K4:O" & lookupLR).Value
            summaryRow.Columns("B").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr 
                                  
            With summaryRow.Range("A1:F1").Borders(xlEdgeTop)
                 .LineStyle = xlContinuous
                 .Weight = xlThin
                 .ColorIndex = xlAutomatic
            End With
            
         End If
    Next ws
    
    With SummaryCodeWS
        .Visible = xlSheetVisible
        .Move After:=Sheets(Sheets.Count)
    End With
    
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
End Sub

When trying to troubleshoot, I would add/remove values from the user selections and I can get it to reset sometimes and show a full "new" summary but when playing it again (unchanged user selections), it will shorten the array to the first or sometimes up to the 2nd value only. I have attached a screenshot to explain the varying array size.

I was playing around with the ubound arguments just to understand it more but it's out of my depth at this point. I haven't changed anything with the syntax as it appears consistent with other upvoted resolutions here.

I hope this makes sense and would appreciate any help!

What does `Debug.Print lookupLR` return? Should you be finding the last row inside the loop? — BigBen, Apr 06 '23 at 18:14
Sorry clicked enter without answering your full question. But the ubound/array is new for me and this is my original code that prompted it: 'Master lookup obt ea. term & code type for lookup lookupWS.Range("G3:H24").Value = ws.Range("D7:E28").Value 'Copy/paste LU table into summary ws lookupWS.Range("K4:O" & lookupLR).Copy SummaryCodeWS.Range("B" & summaryLR).Offset(2, 0).PasteSpecial xlPasteValues — StillLearningThisStuff, Apr 06 '23 at 18:39
'Master lookup obt ea. term & code type for lookup lookupWS.Range("G3:H24").Value = ws.Range("D7:E28").Value — StillLearningThisStuff, Apr 06 '23 at 18:40
'Then copy/paste LU table into summary ws lookupWS.Range("K4:O" & lookupLR).Copy SummaryCodeWS.Range("B" & summaryLR).Offset(2, 0).PasteSpecial xlPasteValues — StillLearningThisStuff, Apr 06 '23 at 18:40
If the last row `lookupLR` is not what you expect, well then there's your problem. — BigBen, Apr 06 '23 at 18:42
So even though it's defined as lookupLR = MasterCodeWS.Range("K10000").End(xlUp).Row, it could still not pick up the changing results that need to be transcribed to the summary ws? — StillLearningThisStuff, Apr 06 '23 at 18:46
I'm still learning all this so is there a route or step(s) I can take to resolve this? — StillLearningThisStuff, Apr 06 '23 at 18:46
Try moving `lookupLR = MasterCodeWS.Range("K10000").End(xlUp).Row` inside the `For Each` loop. — BigBen, Apr 06 '23 at 18:47
omygoodness it worked! Thank you for your time and patience in explaining all of this! — StillLearningThisStuff, Apr 06 '23 at 18:54

score 0 · Accepted Answer · answered Apr 06 '23 at 18:57

0

Seems like you want to find the last row dynamically. Move

lookupLR = LookupWS.Range("K10000").End(xlUp).Row

to immediately before

arr = LookupWS.Range("K4:O" & lookupLR).Value

Side note, if you don't want to hard-code the 10000 (probably a good idea to avoid this), then use:

With LookupWS
   lookupLR = .Cells(.Rows.Count, "K").End(xlUp).Row
End With

answered Apr 06 '23 at 18:57

BigBen

46,229
7
24
40

1

I forget I should post HOW i resolved it with your recommendation but that's exactly where I placed it. And thank you for the additional suggestion. I updated it and re-ran and looks great! – StillLearningThisStuff Apr 06 '23 at 19:04

VBA: Array/Ubound sometimes working - not always returning full array

1 Answers1