excel - “对象变量或未设置块变量”错误来了
问题描述
我正在运行我的代码以从工作簿中查找数据并将其与另一个工作簿同步,并且在运行宏时,我收到此错误。有人可以帮忙吗?谢谢
我正在运行我的代码以从工作簿中查找数据并将其与另一个工作簿同步,并且在运行宏时,我收到此错误。有人可以帮忙吗?谢谢
我正在运行我的代码以从工作簿中查找数据并将其与另一个工作簿同步,并且在运行宏时,我收到此错误。有人可以帮忙吗?谢谢
Sub FindData(wbW As Workbook, WbD As Workbook, ByVal dCol As Long)
Dim wSh As Long
Dim dSh As Long
Dim w As Long, d As Long, c As Long
Dim col As String
Dim co As String
Dim ws As Worksheet
Dim var As Range, coCl As Range
Dim lastColD As Long, lastColW As Long, lastRowW
Dim wsW As Worksheet, wsD As Worksheet
Dim dc As Long, wc As Long
Set ws = ThisWorkbook.Worksheets(1)
Debug.Print WbD.Name
Debug.Print wbW.Name
If wbW.Name = ws.Range("A2") & ".xlsx" And WbD.Name = ws.Range("A4") & ".xlsx" Then
col = "D"
Else
If wbW.Name = ws.Range("A2") & ".xlsx" And WbD.Name = ws.Range("A5") & ".xlsx" Then
col = "G"
Else
If wbW.Name = ws.Range("A2") & ".xlsx" And WbD.Name = ws.Range("A6") & ".xlsx" Then
col = "J"
Else
If wbW.Name = ws.Range("A3") & ".xlsx" And WbD.Name = ws.Range("A7") & ".xlsx" Then
col = "M"
Else
If wbW.Name = ws.Range("A3") & ".xlsx" And WbD.Name = ws.Range("A8") & ".xlsx" Then
col = "P"
End If
End If
End If
End If
End If
wSh = ws.Range(col & 1).End(xlDown).Row
dSh = WbD.Worksheets.Count
For w = 3 To wSh 'Working file sheets listed in macro workbook Sheet1
Set wsW = wbW.Worksheets(ws.Range(col & w).Value)
lastColW = wsW.Cells(2, wsW.Columns.Count).End(xlToLeft).Column
lastRowW = wsW.Cells(wsW.Rows.Count, 2).End(xlUp).Row
For c = 5 To lastRowW 'Companies in working file
co = wsW.Range("B" & c)
For d = 1 To dSh 'Data worksheet
Set coCl = Nothing
Set wsD = WbD.Worksheets(d)
If wsD.Range("A1") = co Then
Set coCl = wsD.Range("A1")
Else
If wsD.Range("A2") = co Then
Set coCl = wsD.Range("A2")
End If
End If
If Not coCl Is Nothing Then
lastColD = wsD.Cells(coCl.Offset(1, 0).Row, wsD.Columns.Count).End(xlToLeft).Column
' If WbD.Name = "2005-2010.xlsx" Then
' yr = "5-10"
' End If
If lastColD = 1 Then
lastColD = wsD.Cells(coCl.Offset(2, 0).Row, wsD.Columns.Count).End(xlToLeft).Column
Set coCl = coCl.Offset(1, 0)
End If
Set var = wsD.Range("A3").CurrentRegion.Columns(1).Find(ws.Range(col & w).Offset(0, 1), , xlValues, xlPart, , , False)
'Debug.Print wsD.Name
For dc = 2 To lastColD
For wc = 5 To lastColW
'Debug.Print wsD.Cells(coCl.Offset(1, 0).Row, dc).Value
'Debug.Print wsW.Cells(2, wc).Value
If wsD.Cells(coCl.Offset(1, 0).Row, dc).Value = wsW.Cells(2, wc).Value Then
'wsD.Range(wsD.Cells(var.Row, 2), wsD.Cells(var.Row, lastColD)).Copy Destination:=wsW.Cells(c, dCol)
' Debug.Print wsD.Name
' Debug.Print wsD.Cells(var.Row, dc).Value
wsW.Cells(c, wc).Value = wsD.Cells(var.Row, dc).Value
End If
Next
Next
Exit For
End If
Next
Next
Next
'Debug.Print WbD.Name
'Debug.Print wbW.Name
End Sub
解决方案
@BruceWayne 的建议是,您需要Find
在以后使用之前验证功能是否成功。
Set Var = wsD.Range("A3").CurrentRegion.Columns(1).Find(ws.Range(col & w).Offset(0, 1), , xlValues, xlPart, , , False)
If Not Var Is Nothing Then ' <-- make sure Find function was able to find a match
' rest of your code goes here
Else
' raise an error message
MsgBox "Unable to find " & ws.Range(col & w).Offset(0, 1) & " in the range specified", vbCritical, "Error!"
End If
If
此外,您可以使用And
toIf
和更改您的倍数Select Case
,如下面的代码所示:
If wbW.Name = ws.Range("A2") & ".xlsx" Then
Select Case WbD.Name ' using Select case can clear and simpify your code
Case ws.Range("A4") & ".xlsx"
col = "D"
Case ws.Range("A5") & ".xlsx"
col = "G"
Case ws.Range("A6") & ".xlsx"
col = "J"
Case ws.Range("A7") & ".xlsx"
col = "MD"
Case ws.Range("A8") & ".xlsx"
col = "P"
End Select
End If
推荐阅读
- .htaccess - 使用重写规则重定向 URL - https://hostname/en/
到 https://hostname/en/ /内容页面> - python - tkinter/python 如何使用 tkinter 使用 python 将文本从一帧传输到另一帧
- c++ - 在 C++ 的 void 函数中可以有很多参数吗?
- ocaml - 在 LearnOcaml 中编译代码时出现“堆栈空间不足”错误
- json - getent passwd 以正确格式输出为 json
- c# - C# 在两个控制器 ActionResults 之间失去价值
- visual-studio-mac - 你如何在 Visual Studio 2017 社区中查看 MAC 的完整错误
- javascript - 我可以让它只在某个变量为真时才执行点击事件吗?
- python - Python使用空格sep从csv读取数据,第一列除外
- azure-machine-learning-workbench - 在本地运行 Azure 机器学习服务管道