VBA 运行时错误 91“未设置对象变量或块变量”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27625751/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Run-Time error 91 "Object variable or with block variable not set"
提问by André Muraro
I have been struggling with this one for a while with no success. I wrote a code to import some data from a database, with the added problem they have different formats. So I wrote a section which would go through the other base and bring data back on the right order (that is not the entire code. I have done all the proper set statements for the objects):
我一直在努力解决这个问题，但没有成功。我编写了一个代码来从数据库导入一些数据，但增加了它们具有不同格式的问题。所以我写了一个部分，它会通过另一个基础并以正确的顺序将数据带回（这不是整个代码。我已经为对象完成了所有正确的设置语句）：
Dim wb_atual, wb_verificar As Workbook
Dim sheet_atual, sheet_verificar As Worksheet
Dim choice, lin_max, lin_b_max, col, col_id, col_max, i, j, k As Integer
Dim is_equal As Boolean
For i = 1 To col_max
col = wb_verificar.Sheets(sheet_verificar.Name).Range("1:1").Find(wb_atual.Sheets(sheet_atual.Name).Cells(2, i).Value, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext).Column
sheet_atual.Cells(j, i).Value = sheet_verificar.Range(sheet_verificar.Cells(j - 1, col), sheet_verificar.Cells(j - 1, col)).Value
lin_b_max = lin_b_max + 1
Next i
That is the block that caused the error, specifically the "col" variable line. What that line does is to look for a column header from a worksheet in another one, and stores the number of the column. That loop works fine for i=1 and 2, but at the third iteration I get the error.
那是导致错误的块，特别是“col”变量行。该行的作用是从另一个工作表的工作表中查找列标题，并存储该列的编号。该循环适用于 i=1 和 2，但在第三次迭代时出现错误。
Thanks in advance for any help
在此先感谢您的帮助
采纳答案by André Muraro
The problem was with the .find method. I could not figure out why it was failing, given the worksheet functions vlookup and match work fine on that cell. I worked around it by writing a basic custom search function to find "col". Another alternative was to use the application.worksheetfunction.matchmethod, but it was slower than the custom function, which makes a noticeable difference, given the size of the initial database.
问题出在 .find 方法上。鉴于工作表函数 vlookup 和 match 在该单元格上工作正常，我无法弄清楚它为什么失败。我通过编写一个基本的自定义搜索函数来查找“col”来解决它。另一种替代方法是使用该application.worksheetfunction.match方法，但它比自定义函数慢，考虑到初始数据库的大小，这会产生显着差异。
回答by Gary's Student
you need to Setwb_verificarto a value between the Dimstatement and any statements that use the Object.
您需要将wb_verificar设置为Dim语句和使用对象的任何语句之间的值。
There may be other errors.
可能还有其他错误。
