excel - 找不到第二个工作簿的最后一行?
问题描述
试图在第二个工作簿上找到最后使用的行,我一直Application-defined or object-defined error
在我的alastRow
变量上得到一个..
Private Sub CommandButton1_Click()
Dim FileToOpen As Variant
Dim xRet As Boolean
Dim Name As String
Dim srcBk As Workbook, srcWS As Worksheet, testWS As Worksheet
Dim c As Range, cR As Range, testRng As Range
Dim mm, nn
Dim arrLoc
Dim alastRow As Long
arrLoc = Array(" A-3", " A-4", " A-5", " A-6", " A-7", " A-8", " A-9", " A-10", " A-11", " A-12", _
" B-1", " B-2", " B-3", " B-4", " B-5", " B-6", " B-7", " B-8", " B-9", " B-10", " B-11", " B-12", _
" C-1", " C-2", " C-3", " C-4", " C-5", " C-6", " C-7", " C-8", " C-9", " C-10", " C-11", " C-12", _
" D-1", " D-2", " D-3", " D-4", " D-5", " D-6", " D-7", " D-8", " D-9", " D-10", " D-11", " D-12", _
" E-1", " E-2", " E-3", " E-4", " E-5", " E-6", " E-7", " E-8", " E-9", " E-10", " E-11", " E-12", _
" F-1", " F-2", " F-3", " F-4", " F-5", " F-6", " F-7", " F-8", " F-9", " F-10", " F-11", " F-12", _
" G-1", " G-2", " G-3", " G-4", " G-5", " G-6", " G-7", " G-8", " G-9", " G-10", " G-11", " G-12", _
" H-1", " H-2", " H-3", " H-4", " H-5", " H-6", " H-7", " H-8", " H-9", " H-10", " H-11", " H-12")
Application.ScreenUpdating = False
FileToOpen = Application.GetOpenFilename(Title:="C:\Users\OneDrive\Documents\BUTTON PRESS TO OPEN", FileFilter:="Excel Files (*.xls*),*xls*")
If FileToOpen <> False Then
Name = CStr(FileToOpen)
xRet = IsWorkBookOpenNow(Name)
Set srcBk = Application.Workbooks.Open(FileToOpen)
Set testWS = ThisWorkbook.Worksheets("Test")
Set srcWS = srcBk.Worksheets(1)
Set testRng = testWS.Columns(1)
alastRow = srcBk.Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Row 'ERROR HERE
srcBk.Sheets(1).Range("B:I").UnMerge 'open user selected file and unmerge columns
testWS.Range("B1").Value = srcWS.Range("E1").End(xlDown).Value
testWS.Range("B3").Value = srcWS.Range("A1:A50").Find(" ", , xlValues).Offset(-1, 0).Value 'find the first cell containing 1 space
For Each c In srcWS.Range("A1:A" & alastRow).Cells
mm = Application.Match(c.Value, arrLoc, 0) 'find cell value containing anything in array
If Not IsError(mm) Then
nn = Application.Match(c.Value, testRng, 0)
Set cR = testWS.Cells(nn, 2)
cR.Value = c.Offset(0, 1).Value
End If
Next c
With testWS.Range("B4:B100")
.WrapText = True
.RowHeight = 15
.ColumnWidth = 27.43
.VerticalAlignment = xlVAlignTop
End With
If xRet <> True Then
srcBk.Close False
End If
End If
Application.ScreenUpdating = True
End Sub
我试过了
For Each c In srcWS.Range(srcWS.Cells(1, 1), srcWS.Cells(Rows.Count, 1)).End(xlUp).Cells
和
alastRow = srcWS.Cells(Rows.Count, 1).End(xlUp).Row
和
alastRow = srcBk.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
我对所有事情都遇到了同样的错误。如果我定义范围srcWS.range("A1:A100")
,它工作得很好。
我注意到的一件事是这个宏所在的工作簿有 1048576 行,而我试图从中获取数据的工作簿有 65536。我认为这无关紧要,但我不知道。
解决方案
请为给出错误的行尝试此代码。
alastRow = rcBk.Worksheets(1).Cells(srcBk.Worksheets(1).Rows.Count, "A").End(xlUp).Row
或者
With srcBk.Worksheets(1)
alastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
不同之处在于在srcBk.Worksheets(1).Rows.Count
您要在其中查找最后一行的同一工作表中进行测量。如果不指定工作表,则将使用 ActiveSheet。在大多数情况下,这没有什么区别,因为您的 Excel 版本创建的所有工作表都具有相同的行数。但是,在这种情况下,您似乎正在查看由另一个旧版本的 Excel 创建的工作表,该版本的行数较少,并且尝试解决不存在的行会导致错误。
推荐阅读
- python-3.x - 类中的键盘事件在 tkinter 中不起作用
- c++ - Stoi 不是 std 的成员
- google-bigquery - 跨各种 BigQuery 表分布数据
- php - 如何在运行 PHP 的 Docker 容器中自动启动 cron?
- sql-server-2014 - 适用于 SQL Server 2014 或更早版本的时态表功能
- reactjs - Redux 减速器状态在安装组件时返回 NaN 值,但在更新时工作正常
- networking - 在动态 IP 上托管我的世界服务器的安全方式
- python - 如何在 pandas.Timestamp.today() 函数中只保留日期 YYYY-MM-DD?
- c++ - 如何从 Windows 为 Linux 编译 C++ 代码(Dev-Cpp)
- c# - 如何判断 Skia 阴影所需的空间量