Excel VBA-查找单元格将值粘贴到
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17935777/
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
Excel VBA- Lookup cell to paste value to
提问by 1212__Hello
I'm trying to build a macro that inserts a value into a specific cell of table by looking up a row value and looking up a column value and using that to determine the cell to paste to. I am quite new to VBA so I'm not really sure what I'm doing.
我正在尝试构建一个宏，通过查找行值和列值并使用它来确定要粘贴到的单元格，从而将值插入到表的特定单元格中。我对 VBA 很陌生，所以我不太确定我在做什么。
This is what I have so far:
这是我到目前为止：
Name=Sheets("Input").Range("C5")
Week=Sheets("Input").Range("C19")
copyCell=Sheets("Input").Range("C18")
pasteCell = Application.Index(Sheets("Resources").Range("B2:AZ50"),Application.Match(Week,Sheets("Resources").Range("A2:A50"),0),Application.Match(Name,Sheets("Resources").Range("B1:AZ1"),0))
copyCell.Copy
pasteCell.PasteSpecial PasteValues
I keep getting a runtime error object required but I'm not quite sure what I'm doing wrong...
我不断收到需要的运行时错误对象，但我不太确定我做错了什么......
Any help would be appreciated!
任何帮助，将不胜感激！
UPDATE
更新
This is the table which the cell is being pasted too (this table is not the real table just an example so as to hide the names in the table but it looks exactly the same except that the real table starts on the first row).
这也是粘贴单元格的表格（这个表格不是真正的表格，只是为了隐藏表格中的名称，但它看起来完全一样，只是真正的表格从第一行开始）。
And this is the input page:
这是输入页面：
Note: I have since put in more code surrounding the week variable:
注意：我已经围绕周变量添加了更多代码：
If WorksheetFunction.CountA(Sheets("Input").Range("C19")) = 0 Then
Week = Sheets("Input").Range("C20").Value
Else
Week = Sheets("Input").Range("C19").Value
End If
Very much appreciate the help you guys are giving!
非常感谢你们提供的帮助！
采纳答案by David Zemens
without seeing more of your code, I assume you have declared copyCelland pasteCellas Type Variant(if it is not declared, it istype Variant).
在没有看到更多代码的情况下，我假设您已将copyCelland声明pasteCell为 Type Variant（如果未声明，则为type Variant）。
This will raise the error you describe.
这将引发您描述的错误。
Resolve it by properly declaring your variables, and using the Setkeyword when assigning object variables.
通过正确声明变量并Set在分配对象变量时使用关键字来解决它。
Additionally, failing to declare Nameand Weekwill raise the 424 Object Requirederror, too. They should be Stringdata type, and assigned using the range .Valuemethod. This error will persist, without more revision to your code, if either of the match functions returns an error (match not found).
此外，未能声明Name并且Week也会引发424 Object Required错误。它们应该是String数据类型，并使用范围.Value方法分配。如果任一匹配函数返回错误（未找到匹配），则此错误将持续存在，无需对您的代码进行更多修改。
I think this will work:
我认为这会奏效：
Sub Test()
Dim Name As String
Dim Week As Long
Dim copyCell As Range
Dim pasteCell As Range
Name = Sheets("Input").Range("C5").Value
Week = CLng(DateValue(Sheets("Input").Range("C19").Value))
Set copyCell = Sheets("Input").Range("C18")
If Not IsError(Application.Match(Week, Sheets("Resources").Range("A2:A50"), 0)) And _
Not IsError(Application.Match(Name, Sheets("Resources").Range("B1:AZ1"), 0)) Then
Set pasteCell = Application.Index(Sheets("Resources").Range("B2:AZ50"), _
Application.Match(Week, Sheets("Resources").Range("A2:A50"), 0), _
Application.Match(Name, Sheets("Resources").Range("B1:AZ1"), 0))
copyCell.Copy
pasteCell.PasteSpecial PasteValues
Else:
MsgBox Name & " and/or " & Week & " not found!", vbInformation
End If
End Sub
UPDATED
更新
Excel doesn't always know what to do with date values. that was the case here. On the worksheet, a date value is stored as a long integer. In the macro, we're referring to it as a string, which can raise several errors including '1004' (unable to match).
Excel 并不总是知道如何处理日期值。这里就是这种情况。在工作表上，日期值存储为长整数。在宏中，我们将其称为字符串，它会引发多个错误，包括“1004”（无法匹配）。
I made a revision to the code above, Dim Week as Longand then changed the assignment to ensure the value is interpreted as a Datefrom the worksheet, and then as a Longinteger when assigned to this variable:
我对上面的代码进行了修改，Dim Week as Long然后更改了赋值以确保将值解释为Date工作表中的 a，然后Long在分配给此变量时解释为整数：
Week = CLng(DateValue(Sheets("Input").Range("C19").Value))
Now, this may not be foolproof if your dates aren't all actually dates (google it...) but this actually happens pretty often some dates are entered as dates (numbers formatted as date) and others are entered as string literals. If that is the case, the Weekvalue should still be handled properly, but the lookup range also should be formatted the same way to avoid potential errors again.
现在，这可能不是万无一失的，如果您的日期并非都是实际日期（谷歌它......），但实际上经常发生这种情况，一些日期作为日期输入（数字格式为日期），而其他日期作为字符串文字输入。如果是这种情况，该Week值仍应正确处理，但查找范围也应以相同的方式格式化以避免再次出现潜在错误。
