Excel VBA 类型不匹配错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16967938/
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 Type mismatch error
提问by user2460606
I am trying to compare the values from one column of one sheet, with the values of another column of a different sheet, same workbook, though. It steps through each cell in the other column, and if the cell value, a string, does not exist in sheet2, then the row from sheet1 is copied over to sheet3. You can think of it like I'm comparing two arrays. I want to see if there are any values in Array1 that do not appear in Array2, and if they do not appear in Array1, the value is copied into Array3.
不过，我正在尝试将一张工作表中一列的值与不同工作表、同一工作簿中另一列的值进行比较。它逐步遍历另一列中的每个单元格，如果单元格值（字符串）在 sheet2 中不存在，则将 sheet1 中的行复制到 sheet3。你可以把它想象成我在比较两个数组。我想看看Array1中是否有没有出现在Array2中的值，如果没有出现在Array1中，则将该值复制到Array3中。
My main issue is I'm getting a type-mismatch error in line 5. The values contain strings. I am fairly new at Excel VBA and am trying to learn it on the fly. Any help would be greatly appreciated.
我的主要问题是第 5 行出现类型不匹配错误。这些值包含字符串。我是 Excel VBA 的新手，正在尝试即时学习。任何帮助将不胜感激。
Sub search()
Dim count As Integer
count = 0
For Each i In Worksheets("Sheet1").Range("C2:C4503")
Set first_cell = Worksheets("Sheet1").Cells(i, 3) <-- Mismatch eror
For Each j In Worksheets("Sheet2").Range("X2:X4052")
Set second_cell = Worksheets("Sheet2").Cells(j, 24)
If second_cell = first_cell Then Exit For
Next j
count = count + 1
Set Worksheets("Sheet3").Cells(count, 1) = Worksheets("Sheet1").Cells(j, 1).Select
Next i
End Sub
结束子
回答by Tim Williams
Sub Search()
Dim rowNum As Long
Dim i As Range, f As Range
rowNum = 1
For Each i In Worksheets("Sheet1").Range("C2:C4503").Cells
If Len(i.Value) > 0 Then
'search for value on sheet2
Set f = Worksheets("Sheet2").Range("X2:X4052").Find( _
i.Value, , xlValues, xlWhole)
If f Is Nothing Then
'not found: copy row from sheet1>sheet3
i.EntireRow.Copy Worksheets("Sheet3").Cells(rowNum, 1)
rowNum = rowNum + 1
End If
End If
Next i
End Sub
回答by Joe
The following:
下列：
For Each i In Worksheets("Sheet1").Range("C2:C4503")
...
Next i
iterates through the cells in the specified range; i is a Rangeobject representing the current cell.
遍历指定范围内的单元格；i 是Range表示当前单元格的对象。
You are using it as in integer index in the following line:
您在以下行的整数索引中使用它：
Set first_cell = Worksheets("Sheet1").Cells(i, 3)
Hence the Type Mismatch.
因此类型不匹配。
