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

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

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

问题

以下是您要翻译的内容：

"Code:

  1. Sub WS_Lookup()
  3.     Dim wb As Workbook
  4.     Dim ws As Worksheet
  5.     Dim LookupWS As Worksheet
  6.     Dim SummaryCodeWS As Worksheet
  7.     Dim lookupLR As Long
  8.     Dim summaryRow As Range
  9.     Dim arr As Variant
  10.     
  11.     Set wb = ActiveWorkbook
  12.     Set LookupWS = wb.Worksheets("Lookup")
  13.     Set SummaryCodeWS = wb.Worksheets("Summary List")
  14.     lookupLR = LookupWS.Range("K10000").End(xlUp).Row
  15.                 
  16.     With Application
  17.         .ScreenUpdating = False
  18.         .EnableEvents = False
  19.     End With
  20.     
  21.     For Each ws In wb.Worksheets
  22.         If ws.Visible = xlSheetVisible And ws.Name <> " " Then
  23.                     
  24.             Set summaryRow = SummaryCodeWS.Range("B10000").End(xlUp).Offset(2).EntireRow
  25.             summaryRow.Columns("A").Value = ws.Range("A4").Value
  26.                     
  27.             LookupWS.Range("G3:H24").Value = ws.Range("D7:E28").Value
  28.                     
  29.             arr = LookupWS.Range("K4:O" & lookupLR).Value
  30.             summaryRow.Columns("B").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr 
  31.                                   
  33.             With summaryRow.Range("A1:F1").Borders(xlEdgeTop)
  34.                 .LineStyle = xlContinuous
  35.                 .Weight = xlThin
  36.                 .ColorIndex = xlAutomatic
  37.             End With
  38.             
  39.         End If
  40.     Next ws
  41.     
  42.     With SummaryCodeWS
  43.         .Visible = xlSheetVisible
  44.         .Move After:=Sheets(Sheets.Count)
  45.     End With
  46.     
  47.     With Application
  48.         .ScreenUpdating = True
  49.         .EnableEvents = True
  50.     End With
  51.     
  52. 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:

  1. Sub WS_Lookup()
  3.     Dim wb As Workbook
  4.     Dim ws As Worksheet
  5.     Dim LookupWS As Worksheet
  6.     Dim SummaryCodeWS As Worksheet
  7.     Dim lookupLR As Long
  8.     Dim summaryRow As Range
  9.     Dim arr As Variant
  10.     
  11.     Set wb = ActiveWorkbook
  12.     Set LookupWS = wb.Worksheets(&quot;Lookup&quot;)
  13.     Set SummaryCodeWS = wb.Worksheets(&quot;Summary List&quot;)
  14.     lookupLR = LookupWS.Range(&quot;K10000&quot;).End(xlUp).Row
  15.                 
  16.     With Application
  17.         .ScreenUpdating = False
  18.         .EnableEvents = False
  19.     End With
  20.     
  21.     For Each ws In wb.Worksheets
  22.         If ws.Visible = xlSheetVisible And ws.Name &lt;&gt; &quot; &quot; Then
  23.                     
  24.             Set summaryRow = SummaryCodeWS.Range(&quot;B10000&quot;).End(xlUp).Offset(2).EntireRow
  25.             summaryRow.Columns(&quot;A&quot;).Value = ws.Range(&quot;A4&quot;).Value
  26.                     
  27.             LookupWS.Range(&quot;G3:H24&quot;).Value = ws.Range(&quot;D7:E28&quot;).Value
  28.                     
  29.             arr = LookupWS.Range(&quot;K4:O&quot; &amp; lookupLR).Value
  30.             summaryRow.Columns(&quot;B&quot;).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr 
  31.                                   
  32.             With summaryRow.Range(&quot;A1:F1&quot;).Borders(xlEdgeTop)
  33.                  .LineStyle = xlContinuous
  34.                  .Weight = xlThin
  35.                  .ColorIndex = xlAutomatic
  36.             End With
  37.             
  38.          End If
  39.     Next ws
  40.     
  41.     With SummaryCodeWS
  42.         .Visible = xlSheetVisible
  43.         .Move After:=Sheets(Sheets.Count)
  44.     End With
  45.     
  46.     With Application
  47.         .ScreenUpdating = True
  48.         .EnableEvents = True
  49.     End With
  50.     
  51. 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

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

to immediately before

  1. 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:

  1. With LookupWS
  2.    lookupLR = .Cells(.Rows.Count, &quot;K&quot;).End(xlUp).Row
  3. End With

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

发表评论

匿名网友

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

确定