vba 对于每个控制变量必须是变体或对象
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16172608/
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
for each control variable must be variant or object
提问by user2311821
Really new to VBA here... I've looked around and tried to piece together some code to fulfil my need. Think it's almost there, but I'm getting errors that are likely easy to overcome and yet I don't know how.
在这里对 VBA 真的很陌生......我环顾四周并试图拼凑一些代码来满足我的需要。认为它几乎就在那里，但我遇到了很可能很容易克服的错误，但我不知道如何克服。
The code looks at the current sheet (STOCK), and takes a 'target' text value from cell A2. It then searches a named range in another sheet 'Other'. If it determines one of the cells ('cand') in Other to be equal to the target value, then a value of "True" will be applied to column G in the STOCK sheet, on the same row of the original target.
该代码查看当前工作表 (STOCK)，并从单元格 A2 中获取“目标”文本值。然后它在另一个工作表“其他”中搜索命名范围。如果它确定 Other 中的一个单元格 ('cand') 等于目标值，则值“True”将应用于原始目标的同一行上的 STOCK 表中的 G 列。
Hopefully this makes sense. I've copied in the code which will maybe shed more light on things.
希望这是有道理的。我已经复制了代码，这可能会更清楚地说明事情。
Dim target As String
Dim cand As String
Dim currentrow As Integer
Sub search_named_range()
' This range is hard coded; we can try A:A if hard code version works '
For Each target In Worksheets("STOCK").Range("A2:A1000")
' retrieve the row of the current range, for use when setting target values '
currentrow = Range(target).Row
' FOR loop to search range of part numbers in Mojave '
For Each cand In Worksheets("Other").Range("N9:N150")
If StrConv(cand.Value, 2) = StrConv(target, 2) Then
Worksheets("STOCK").Range("G" + currentrow) = "True"
GoTo FORend
End If
Next cand
' If part is not found, do nothing and return to find next target '
FORend: Next target
End Sub
Currently I'm getting the error 'For Each control variable must be Variant or Object', but can't find anywhere that explains why this is. I'm sure it's pretty obvious, but a steer would be really appreciated.
目前我收到错误“每个控制变量必须是变体或对象”，但找不到任何解释原因的地方。我敢肯定这很明显，但真的很感激。
Thanks.
谢谢。
回答by Dick Kusleika
You can't use a String variable in a For Each. You're using tartgetand candas the control variables in your For Each loops but you have defined them as strings. They need to be an object, and specifically an object that is contained the collection of objects you're iterating. You're iterating over a range, which is a collection of ranges, so your control variables need to be Range objects.
您不能在 For Each 中使用 String 变量。您在 For Each 循环中使用tartgetandcand作为控制变量，但您已将它们定义为字符串。它们必须是一个对象，特别是一个包含您正在迭代的对象集合的对象。您正在迭代一个范围，这是一个范围的集合，因此您的控制变量需要是 Range 对象。
Sub search_named_range()
Dim rCell As Range
Dim rCand As Range
For Each rCell In Worksheets("STOCK").Range("A2:A1000").Cells
For Each rCand In Worksheets("Other").Range("N9:N150").Cells
If StrComp(rCand.Value, rCell.Value, vbTextCompare) = 0 Then
rCell.Offset(0, 6).Value = "True"
Exit For 'exits the rCand For, but no the rCell one
End If
Next rCand
Next rCell
End Sub
Other changes that weren't correcting errors:
其他未纠正错误的更改：
I'm not sure why you declared your variables outside the sub, but I put them inside.
我不确定你为什么在 sub 之外声明你的变量，但我把它们放在里面。
You don't need to define .Cellsat the end of the For Each line, but I like to. You could iterate over .Rowsor .Columnsor .Areaswith a Range (although .Cellsis the default).
您不需要.Cells在 For Each 行的末尾进行定义，但我喜欢这样做。你可以遍历.Rows或.Columns或.Areas有范围（尽管.Cells是默认值）。
There's nothing wrong with StrConvert, but you could also use LCase() or, as I do, StrComp.
StrConvert 没有任何问题，但您也可以使用 LCase()，或者像我一样使用 StrComp。
Since I already have a reference to a cell on the current row (rCell), I use that and Offset to fill in a column I want.
由于我已经有对当前行 (rCell) 上的单元格的引用，因此我使用它和 Offset 来填充我想要的列。
