英文:
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.
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!
答案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("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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论