记录计数函数给我一个运行时错误"1004"：对象"_Global"的方法'Range'在 excel 中失败

Question

ActiveWorkbook.Names.Add Name:="RecordCount", _
RefersToR1C1:="=qryTC2015ContainerUtilizationSh!R1C100"
Range("CV1").Select
ActiveCell.FormulaR1C1 = "=COUNTA(C[-99])"
RecordCount = Range("RecordCount")




Range("A2:N" & RecordCount).Select

'Range(Selection, Selection.End(xlDown)).Select

Selection.Copy

Windows("TC Containers Shipped Report Template.xlsm").Activate

Sheets("Container Utilization").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

问题是什么，如何解决?

Answer 1

看起来您正在期待该行之外的数字，而您所做的只是引用范围。如果你想要记录的数量，你需要它是' Range("RecordCount"). count "，它将返回区域中的单元格数量，而不是有值的单元格数量。如果您想要N列中的记录数，最好像前面建议的那样查找最后一行。

Dim LastRow as Long
LastRow = Cells(Rows.Count, "N").End(xlUp).Row
Range("A2:N" & LastRow).Copy
Workbooks("TC Containers Shipped Report Template").Sheets("Container Utilization").Range("A2").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False