VBA: Array/Ubound 有时有效 – 不总是返回完整数组

huangapple go评论54阅读模式
英文:

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

问题

以下是您要翻译的内容:

"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!"

请注意,代码部分不被翻译。

英文:

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: https://stackoverflow.com/questions/75837468/vba-worksheet-loop-only-staying-on-the-active-sheet-instead-of-looping-through

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(&quot;Lookup&quot;)
    Set SummaryCodeWS = wb.Worksheets(&quot;Summary List&quot;)
    lookupLR = LookupWS.Range(&quot;K10000&quot;).End(xlUp).Row
                
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    For Each ws In wb.Worksheets
        If ws.Visible = xlSheetVisible And ws.Name &lt;&gt; &quot; &quot; Then
                    
            Set summaryRow = SummaryCodeWS.Range(&quot;B10000&quot;).End(xlUp).Offset(2).EntireRow
            summaryRow.Columns(&quot;A&quot;).Value = ws.Range(&quot;A4&quot;).Value
                    
            LookupWS.Range(&quot;G3:H24&quot;).Value = ws.Range(&quot;D7:E28&quot;).Value
                    
            arr = LookupWS.Range(&quot;K4:O&quot; &amp; lookupLR).Value
            summaryRow.Columns(&quot;B&quot;).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr 
                                  
            With summaryRow.Range(&quot;A1:F1&quot;).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!

VBA: Array/Ubound 有时有效 – 不总是返回完整数组

答案1

得分: 0

将以下代码部分翻译为中文:

"Seems like you want to find the last row dynamically. Move" 可能你想要动态查找最后一行。将

"lookupLR = LookupWS.Range("K10000").End(xlUp).Row" 移动到紧接着

"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:" 顺便说一下,如果你不想硬编码10000(很可能是个好主意),那么可以使用:

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

英文:

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

lookupLR = LookupWS.Range(&quot;K10000&quot;).End(xlUp).Row

to immediately before

arr = LookupWS.Range(&quot;K4:O&quot; &amp; 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, &quot;K&quot;).End(xlUp).Row
End With

huangapple
  • 本文由 发表于 2023年4月7日 01:24:42
  • 转载请务必保留本文链接:https://go.coder-hub.com/75952213.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定