我使用insert / table命令在excel中创建了两个命名表，并将它们命名如下。在Excel中，一切看起来都很好。表House_details的行计数是50，包括标题行，而Sale_price_schedule 15的行计数包括标题行。
我相关的VBA代码是
Set tblHouseDetails = Sheets("House details").ListObjects("House_details")
Set tblSalePriceSchedule = Sheets("Sale price schedule").ListObjects("Sale_price_schedule")
'Find the product type, look for the relevant price and insert it into the appropriate house details column
With tblHouseDetails.DataBodyRange
For iCount = 2 To Rows.Count
iTemp = Rows.Count
strProductType = Cells(iCount, 5).Value 'The fifth columns is the product type
dSaleDate = Cells(iCount, 3).Value 'The third column is the sale date
'Find the correct sale price in the SalePriceSchedule
'----------------------------
dblSalePrice = -999
With tblSalePriceSchedule.DataBodyRange
iTemp = Rows.Count
For iCount2 = 2 To Rows.Count
If Cells(iCount2, 3) = strProductType And Cells(iCount2, 1) <= dSaleDate And Cells(iCount2, 2) >= dSaleDate Then
dblSalePrice = Cells(iCount2, 4)
End If
Next iCount2
End With
'------------------------------
Cells(iCount, 6) = dblSalePrice
Next iCount
End With
我的问题是tblHouseDetails行数不是我在Excel中定义的50行，而是整个工作表（1048576）。
为什么我的Rows.Count不正确？
当单独使用“行”和“单元格”时，它们指的是“ ActiveSheet.Rows”和“ ActiveSheet.Cells”。
如果要引用“ With.DataBodyRange”中的范围，则需要在前面添加一个点（“ .Rows”，“。Cells”）：
Set tblHouseDetails = Sheets("House details").ListObjects("House_details")
Set tblSalePriceSchedule = Sheets("Sale price schedule").ListObjects("Sale_price_schedule")
'Find the product type, look for the relevant price and insert it into the appropriate house details column
With tblHouseDetails.DataBodyRange
For iCount = 1 To .Rows.Count
iTemp = .Rows.Count
strProductType = .Cells(iCount, 5).Value 'The fifth columns is the product type
dSaleDate = .Cells(iCount, 3).Value 'The third column is the sale date
'Find the correct sale price in the SalePriceSchedule
'----------------------------
dblSalePrice = -999
With tblSalePriceSchedule.DataBodyRange
iTemp = .Rows.Count
For iCount2 = 1 To .Rows.Count
If .Cells(iCount2, 3) = strProductType And .Cells(iCount2, 1) <= dSaleDate And .Cells(iCount2, 2) >= dSaleDate Then
dblSalePrice = .Cells(iCount2, 4)
End If
Next iCount2
End With
'------------------------------
.Cells(iCount, 6) = dblSalePrice
Next iCount
End With
但是更好的解决方案是将行用作变量：
Dim rowsA As Range, rowsB As Range
Set rowsA = tblHouseDetails.DataBodyRange.Rows
Set rowsB = tblSalePriceSchedule.DataBodyRange.Rows
'Find the product type, look for the relevant price and insert it into the appropriate house details column
For i = 1 To rowsA.Count
strProductType = rowsA.Cells(i, 5).Value 'The fifth columns is the product type
dSaleDate = rowsA.Cells(i, 3).Value 'The third column is the sale date
'Find the correct sale price in the SalePriceSchedule
dblSalePrice = -999
For j = 1 To rowsB.Count
If rowsB.Cells(j, 3) = strProductType And rowsB.Cells(j, 1) <= dSaleDate And rowsB.Cells(j, 2) >= dSaleDate Then
rowsA.Cells(i, 6) = rowsB.Cells(j, 4)
End If
Next
Next
如果您正在寻找一种高性能的解决方案，那么我将使用数组中的值：
Dim dataA(), dataB(), i&, j&, strProductType$, dSaleDate, dblSalePrice
' Get all the values in an array
dataA = Sheets("House details").ListObjects("House_details").DataBodyRange.Value
dataB = Sheets("Sale price schedule").ListObjects("Sale_price_schedule").DataBodyRange.Value
For i = 0 To UBound(dataA)
strProductType = dataA(i, 4) 'The fifth columns is the product type
dSaleDate = dataA(i, 2) 'The third column is the sale date
'Find the correct sale price in the SalePriceSchedule
dblSalePrice = -999
For j = 0 To UBound(dataB)
If dataB(j, 2) = strProductType And dataB(j, 0) <= dSaleDate And dataB(j, 1) >= dSaleDate Then
dataA(i, 5) = dataB(j, 3)
End If
Next
Next
' Copy the values back to the sheet
Sheets("House details").ListObjects("House_details").DataBodyRange.Value = dataA
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句