VBA 中的类型不匹配错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18896321/
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
Type mismatch error in VBA
提问by srt
1.Dim destbook As Workbook
2.Dim destsheet As Worksheet
3.Set destbook = Workbooks("Book1")
4.Set destsheet = destbook.Sheets(1)
5.Workbooks("Book1").Sheets("Sheet1").Range("C6").Select
6.ct = Range(Selection, Selection.End(xlDown)).count + 1
7.destbook.Activate
8.Workbooks(destbook).Sheets(destsheet).Range("A" + ct).Select
9.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Here, when i execute this code, it shows an error saying "type mismatch" on line 8.
在这里，当我执行这段代码时，它在第 8 行显示一个错误，指出“类型不匹配”。
Can u help??...
你能帮忙吗？？...
回答by Sathish Kothandam
You should change + to &
您应该将 + 更改为 &
Workbooks(destbook).Sheets(destsheet).Range("A" & ct).Select
工作簿(destbook).Sheets(destsheet).Range("A" & ct).Select
回答by rory.ap
You are using "destbook" and "destsheet" as the indexes for "Workbooks" and "Sheets", but they are actually themselves of type "Workbook" and "Worksheet" as you've defined them in lines 1 and 2. Change line 8 to: destsheet.Range("A" + ct).Select.
您正在使用“destbook”和“destsheet”作为“Workbooks”和“Sheets”的索引，但它们实际上是您在第 1 行和第 2 行中定义的“Workbook”和“Worksheet”类型。更改行8 到：destsheet.Range("A" + ct).Select。
回答by chewmewaba4
As most folks have already pointed out, you need to change the way you are referencing the desired destination cell. Either you can switch over to an ampersand (&), or change to just a Cells(row,col) reference as you are only updating a single cell (see code below). You should also consider slimming down your code to make it a bit more efficient.
正如大多数人已经指出的那样，您需要更改引用所需目标单元格的方式。您可以切换到与号 (&)，也可以更改为 Cells(row,col) 引用，因为您只更新单个单元格（请参阅下面的代码）。您还应该考虑精简代码以使其更高效。
Dim destbook As Workbook
Dim destsheet As Worksheet
Set destbook = Workbooks("Book1")
Set destsheet = destbook.Sheets(1)
'See my note below
destbook.Activate
destsheet.Range("C6").Select
ct = Range(Selection, Selection.End(xlDown)).Count + 1
destsheet.Cells(ct, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Notes: - Line 5 should be changed to use your variables destbook and destsheet. Note that you'll need to move line 7 up to initially Activate your Workbook and then you can reference your Worksheet destsheet. - At the "see my note below", you should probably be copying some value from somewhere, otherwise you'll run into a new error upon your PasteSpecial command. - You should combine line 8 and line 9 together, unless you are planning on reusing the selection from line 8 in some other code (that you have not provided here).
注意： - 应更改第 5 行以使用您的变量 destbook 和 destsheet。请注意，您需要将第 7 行向上移动以最初激活您的工作簿，然后您可以引用您的工作表目标表。- 在“请参阅下面的注释”中，您可能应该从某处复制一些值，否则您会在执行 PasteSpecial 命令时遇到新错误。- 您应该将第 8 行和第 9 行组合在一起，除非您计划在其他一些代码中重用第 8 行的选择（您没有在此处提供）。
Hope this helps.
希望这可以帮助。
