In my VBA code using excel I have
在我使用excel的VBA代码中
Dim Field2 As String
Field2 = Cells(i, 4).Value
If Right(Field2, 3) = ("A-1" Or "A-2" Or "B-1" Or "B-2" Or "C-1" Or "C-2" Or "D-1" Or "D-2" Or "D-3") Then
Cells(i, 50).Value = "OtherPrtnrs /Dcrs & Dept heads"
End If
An when I run the code, I get the follow message: "Run-time error '13': Type mismatch"
当我运行代码时,我收到以下消息:“运行时错误'13':类型不匹配”
Do you have any idea how to make this code work?
你知道如何使这个代码工作吗?
Thank you
Fabio
2 个解决方案
#1
5
Try this
If Right(Field2, 3) = "A-1" Or _
Right(Field2, 3) = "A-2" Or _
Right(Field2, 3) = "B-1" Or _
Right(Field2, 3) = "B-2" Or _
Right(Field2, 3) = "C-1" Or _
Right(Field2, 3) = "C-2" Or _
Right(Field2, 3) = "D-1" Or _
Right(Field2, 3) = "D-2" Or _
Right(Field2, 3) = "D-3" Then
Cells(i, 50).Value = "OtherPrtnrs /Dcrs & Dept heads"
End If
Or better still... this
或者更好......这个
Select Case Right(Field2, 3)
Case "A-1","A-2","B-1","B-2","C-1","C-2","D-1","D-2","D-3"
Cells(i, 50).Value = "OtherPrtnrs /Dcrs & Dept heads"
End Select
Note: I am assuming that i
is a valid row number
注意:我假设我是一个有效的行号
Explanation:
When comparing using an If
statement, you cannot say If A = B or C
. You are supposed to compare is separately. If A = B or A = C Then
. Here each OR
is its own Boolean
statement i.e it will be evaluated separately.
当使用If语句进行比较时,您不能说如果A = B或C.您应该单独比较。如果A = B或A = C那么。这里每个OR都是它自己的布尔语句,即它将被单独评估。
When you have multiple such comparisons, it is better to use a Select Statement as shown in the example above.
当您进行多次此类比较时,最好使用Select语句,如上例所示。
#2
0
Here are the alternatives:
以下是替代方案:
You may use the Excel OR function as follows
您可以使用Excel OR函数,如下所示
Dim Str As String, ChkStr As Boolean
Field2 = Cells(i, 4)
Str = Right(Field2, 3)
ChkStr = WorksheetFunction.Or(Str = "A-1", Str = "A-2", Str = "B-1", Str = "B-2", Str = "C-1", _
Str = "C-2", Str = "D-1", Str = "D-2", Str = "D-3")
If ChkStr Then 'Alternatively: If ChkStr = True Then
Cells(i, 50) = "OtherPrtnrs /Dcrs & Dept heads"
End If
or a slightly different approach from Siddharth Rout's answer using the benefit of the Select Case statement on text, especially for "D-1"
, "D-2"
, and "D-3"
, to determine if the condition lies between other text in an alphabetical sense. Caution: this can at times cause unexpected results so make sure you test it beforehand.
或者与Siddharth Rout的答案略有不同的方法,使用Select Case语句对文本的好处,特别是对于“D-1”,“D-2”和“D-3”,以确定条件是否介于其他文本之间按字母顺序排列。注意:这有时会导致意外结果,因此请务必事先进行测试。
Select Case Right(Field2, 3)
Case "A-1" To "A-2", "B-1" To "B-2", "C-1" To "C-2", "D-1" To "D-3"
Cells(i, 50) = "OtherPrtnrs /Dcrs & Dept heads"
End Select
The last but not the least is to use For Each...Next statement of an array
最后但并非最不重要的是使用For Each ... Next数组的语句
StrList = Array("A-1", "A-2", "B-1", "B-2", "C-1", "C-2", "D-1", "D-2", "D-3")
For Each element In StrList
If Right(Field2, 3) = element Then
Cells(i, 50) = "OtherPrtnrs /Dcrs & Dept heads"
Exit For
End If
Next
One point that should be made here is that, by default, VBA code is case sensitive.
这里应该做的一点是,默认情况下,VBA代码区分大小写。
#1
5
Try this
If Right(Field2, 3) = "A-1" Or _
Right(Field2, 3) = "A-2" Or _
Right(Field2, 3) = "B-1" Or _
Right(Field2, 3) = "B-2" Or _
Right(Field2, 3) = "C-1" Or _
Right(Field2, 3) = "C-2" Or _
Right(Field2, 3) = "D-1" Or _
Right(Field2, 3) = "D-2" Or _
Right(Field2, 3) = "D-3" Then
Cells(i, 50).Value = "OtherPrtnrs /Dcrs & Dept heads"
End If
Or better still... this
或者更好......这个
Select Case Right(Field2, 3)
Case "A-1","A-2","B-1","B-2","C-1","C-2","D-1","D-2","D-3"
Cells(i, 50).Value = "OtherPrtnrs /Dcrs & Dept heads"
End Select
Note: I am assuming that i
is a valid row number
注意:我假设我是一个有效的行号
Explanation:
When comparing using an If
statement, you cannot say If A = B or C
. You are supposed to compare is separately. If A = B or A = C Then
. Here each OR
is its own Boolean
statement i.e it will be evaluated separately.
当使用If语句进行比较时,您不能说如果A = B或C.您应该单独比较。如果A = B或A = C那么。这里每个OR都是它自己的布尔语句,即它将被单独评估。
When you have multiple such comparisons, it is better to use a Select Statement as shown in the example above.
当您进行多次此类比较时,最好使用Select语句,如上例所示。
#2
0
Here are the alternatives:
以下是替代方案:
You may use the Excel OR function as follows
您可以使用Excel OR函数,如下所示
Dim Str As String, ChkStr As Boolean
Field2 = Cells(i, 4)
Str = Right(Field2, 3)
ChkStr = WorksheetFunction.Or(Str = "A-1", Str = "A-2", Str = "B-1", Str = "B-2", Str = "C-1", _
Str = "C-2", Str = "D-1", Str = "D-2", Str = "D-3")
If ChkStr Then 'Alternatively: If ChkStr = True Then
Cells(i, 50) = "OtherPrtnrs /Dcrs & Dept heads"
End If
or a slightly different approach from Siddharth Rout's answer using the benefit of the Select Case statement on text, especially for "D-1"
, "D-2"
, and "D-3"
, to determine if the condition lies between other text in an alphabetical sense. Caution: this can at times cause unexpected results so make sure you test it beforehand.
或者与Siddharth Rout的答案略有不同的方法,使用Select Case语句对文本的好处,特别是对于“D-1”,“D-2”和“D-3”,以确定条件是否介于其他文本之间按字母顺序排列。注意:这有时会导致意外结果,因此请务必事先进行测试。
Select Case Right(Field2, 3)
Case "A-1" To "A-2", "B-1" To "B-2", "C-1" To "C-2", "D-1" To "D-3"
Cells(i, 50) = "OtherPrtnrs /Dcrs & Dept heads"
End Select
The last but not the least is to use For Each...Next statement of an array
最后但并非最不重要的是使用For Each ... Next数组的语句
StrList = Array("A-1", "A-2", "B-1", "B-2", "C-1", "C-2", "D-1", "D-2", "D-3")
For Each element In StrList
If Right(Field2, 3) = element Then
Cells(i, 50) = "OtherPrtnrs /Dcrs & Dept heads"
Exit For
End If
Next
One point that should be made here is that, by default, VBA code is case sensitive.
这里应该做的一点是,默认情况下,VBA代码区分大小写。