vba 使用两个字符串变量键入不匹配错误 InStr
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27246132/
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 InStr with two string variables
提问by dagan
I am trying to to check if a current value in a string already exists in a given string. I am using two variables for the parent string(existing string in a cell, active cell) and the search string . If the current string exactly exists in the parent string then a message box pops asking if the user wants to paste the same content .
我正在尝试检查字符串中的当前值是否已存在于给定字符串中。我为父字符串（单元格中的现有字符串，活动单元格）和搜索字符串使用了两个变量。如果当前字符串完全存在于父字符串中，则会弹出一个消息框，询问用户是否要粘贴相同的内容。
I get a type mismatch error at the first IF condition . Can you please help me understand what the issue is ?
我在第一个 IF 条件时收到类型不匹配错误。你能帮我理解问题是什么吗？
If (InStr(ActiveCell.Value, NRemark, vbBinaryCompare)) = 0 Then
Exit Sub
Else
Lresponse = MsgBox("You have already synced the same content . Would you like to continue ?", vbYesNo)
If Lresponse = vbYes Then
GoTo V1 ' goes to the loop to append NRemark to the parent string
Else
Exit Sub
End If
End If
回答by Matt Beeman
Way late on the answer here, but ran into this same problem today - because the ActiveCell.Value is a Variant (Type Undetermined), the system sees that as you trying to use the optional [start] variable, so the type mismatch actually occurs because it thinks your compare variable should be a string.
在这里的答案迟到了，但今天遇到了同样的问题 - 因为 ActiveCell.Value 是一个变体（类型未确定），系统会在您尝试使用可选的 [start] 变量时看到这一点，因此实际上发生了类型不匹配因为它认为您的比较变量应该是一个字符串。
I was using cel.FormulaR1C1 to try to manipulate a variable and that is also a Variant type, so it caused this same problem.
我正在使用 cel.FormulaR1C1 尝试操作一个变量，它也是一个 Variant 类型，所以它导致了同样的问题。
回答by Pawe?
Take a look at arguments in InStr:
看看 InStr 中的参数：
Function InStr([Start], [String1], [String2], [Compare As VbCompareMethod = vbBinaryCompare])
函数 InStr([Start], [String1], [String2], [Compare As VbCompareMethod = vbBinaryCompare])
The first argument is [Start], which in your case should probably be a constant of 1 value.
第一个参数是 [Start]，在您的情况下，它可能应该是一个值为 1 的常量。
If (InStr(1, ActiveCell.Value, NRemark, vbBinaryCompare)) = 0 Then ...
如果 (InStr(1, ActiveCell.Value, NRemark, vbBinaryCompare)) = 0 那么 ...
This should work, at least temporarily, until you encounter empty cells, cells with errors, etc., in which case it will break again.
这应该可以工作，至少是暂时的，直到您遇到空单元格、有错误的单元格等，在这种情况下它会再次中断。
Also, I can't tell what type your NRemark variable is. It has to be a string, so if it's a cell, you should put NRemark.Value instead.
另外，我不知道您的 NRemark 变量是什么类型。它必须是一个字符串，所以如果它是一个单元格，你应该把 NRemark.Value 代替。
