提问人：Jimmy BR 提问时间：10/20/2022 最后编辑：TinManJimmy BR 更新时间：10/20/2022 访问量：46
如果 Colum A 的最后一行为空，则 VBA 索引 Marco 无法自动填充数据
VBA Index Marco can not auto fill data if the last row of Colum A is blank
问：
我在自动填充另一张工作表的数据时遇到了问题，我尝试在工作表（报告）中输入“sku”值，然后从另一张工作表（SOH）中自动填写“商店名称”和“数量”。但是，如果“商店名称”的最后一行（A 列，报告表）= 空白，则此 Marco 将无法正常工作，否则工作正常。我错过了什么吗？任何帮助将不胜感激！
Sub Fill_Report()
Dim d, s As Long
Dim sQTY As Double
Dim dws, sws As Worksheet
Set dws = ThisWorkbook.Worksheets("Report") 'Destination Sheet
Set sws = ThisWorkbook.Worksheets("SOH") 'Source Sheet
dlr = dws.Cells(Rows.Count, 1).End(xlUp).Row
slr = sws.Cells(Rows.Count, 1).End(xlUp).Row
For d = 2 To dlr
For s = 2 To slr
ssku = sws.Cells(s, "A:A").Value
dsku = dws.Cells(d, "B:B").Value
'Index qty from source
sQTY = Application.IfError(Application.Index(Sheets("SOH").Range("A:Z"), _
Application.Match(ssku, Sheets("Report").Range("B:B"), 0), 2), 0)
'add title
dws.Cells(1, 1).Value = "Sotre Name"
dws.Cells(1, 2).Value = "sku"
dws.Cells(1, 3).Value = "qty"
If dsku = ssku Then
dws.Cells(d, "A").Value = "ABC"
dws.Cells(d, "C").Value = sQTY
Exit For
End If
Next s
Next d
End Sub
答：
2赞
TinMan
10/20/2022
#1
集合和词典针对快速查找进行了优化。请考虑在 Match 和 Index 上使用它们。
Range("A1").CurrentRegion将选择整个连续单元格范围。
Sub Fill_Report()
Dim Quantities As New Collection
Set Quantities = getSKUQuantity
Dim Data As Variant
Data = wsReport.Range("A1").CurrentRegion.Columns("B").Offset(1)
Dim r As Long
Dim QTY As Double
For r = r To UBound(Data)
On Error Resume Next
QTY = Quantities(Data(r, 1))
If Err.Number = 0 Then
Data(r, 1) = QTY
Else
Data(r, 1) = ""
End If
On Error GoTo 0
Next
wsReport.Range("A1").CurrentRegion.Columns("C").Offset(1).Value = Data
End Sub
Function getSKUQuantity() As Collection
Dim Data As Variant
Data = wsSOH.Range("A1").CurrentRegion
Dim Quantities As New Collection
Dim r As Long
For r = 2 To UBound(Data)
On Error Resume Next
If Err.Number = 0 Then
Quantities.Add Data(r, 2), CStr(Data(r, 1))
Else
Debug.Print "Duplicate SKU: ", Data(r, 1)
End If
On Error GoTo 0
Next
Set getSKUQuantity = Quantities
End Function
Function wsSOH() As Worksheet
Set wsSOH = ThisWorkbook.Sheets("SOH")
End Function
Function wsReport() As Worksheet
Set wsReport = ThisWorkbook.Sheets("Report")
End Function
评论