vba 如何在 IF 语句中使用 AND
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9140318/
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
How to use AND in IF Statement
提问by user823911
I want to check:
我想检查：
IFcells (i,"A") contains the text 'Miami' AND(i,"D") contains the text 'Florida' THENchange value of cell (i,"C") to BA.
IF单元格 (i,"A") 包含文本 'Miami' AND(i,"D") 包含文本 'Florida' THEN将单元格 (i,"C") 的值更改为 BA。
Sub ABC()
Dim wsh As Worksheet, i As Long, lngEndRowInv As Long
Set wsh = ActiveSheet
i = 2
lngEndRowInv = wsh.Range("A" & wsh.Rows.Count).End(xlUp).Row
While i <= lngEndRowInv
If Cells(i, "A") like "*Miami*" And Cells(i, "D") like "*Florida*" Then
Cells(i, "C").Value = "BA"
End If
i = i + 1
Wend
End Sub
回答by Tony Dallimore
Brief syntax lesson
简短的语法课
Cells(Row, Column)identifies a cell. Row must be an integer between 1 and the maximum for version of Excel you are using. Column must be a identifier (for example: "A", "IV", "XFD") or a number (for example: 1, 256, 16384)
Cells(Row, Column)标识一个单元格。对于您使用的 Excel 版本，行必须是介于 1 和最大值之间的整数。列必须是标识符（例如：“A”、“IV”、“XFD”）或数字（例如：1、256、16384）
.Cells(Row, Column)identifies a cell within a sheet identified in a earlier With statement:
.Cells(Row, Column)标识在早期 With 语句中标识的工作表中的单元格：
With ActiveSheet
:
.Cells(Row,Column)
:
End With
If you omit the dot, Cells(Row,Column)is within the active worksheet. So wsh = ActiveWorkbookwsh.Rangeis not strictly necessary. However, I always use a With statement so I do not wonder which sheet I meant when I return to my code in six months time. So, I would write:
如果省略点，Cells(Row,Column)则在活动工作表内。所以wsh = ActiveWorkbookwsh.Range不是绝对必要的。但是，我总是使用 With 语句，所以当我在六个月后返回我的代码时，我不知道我指的是哪张表。所以，我会写：
With ActiveSheet
:
.Range.
:
End With
Actually, I would not write the above unless I really did want the code to work on the active sheet. What if the user has the wrong sheet active when they started the macro. I would write:
实际上，除非我确实希望代码在活动工作表上工作，否则我不会写上面的内容。如果用户在启动宏时激活了错误的工作表怎么办。我会写：
With Sheets("xxxx")
:
.Range.
:
End With
because my code only works on sheet xxxx.
因为我的代码只适用于工作表 xxxx。
Cells(Row,Column)identifies a cell. Cells(Row,Column).xxxx identifies a property of the cell. Valueis a property. Value is the default property so you can usually omit it and the compiler will know what you mean. But in certain situations the compiler can be confused so the advice to include the .Valueis good.
Cells(Row,Column)标识一个单元格。Cells(Row,Column).xxxx 标识单元格的属性。 Value是财产。Value 是默认属性，因此您通常可以省略它，编译器会知道您的意思。但在某些情况下，编译器可能会感到困惑，因此包含 的建议.Value是好的。
Cells(Row,Column) like "*Miami*"will give True if the cell is "Miami", "South Miami", "Miami, North" or anything similar.
Cells(Row,Column) like "*Miami*"如果单元格是“Miami”、“South Miami”、“Miami, North”或任何类似的东西，将给出 True 。
Cells(Row,Column).Value = "Miami"will give True if the cell is exactly equal to "Miami". "MIAMI" for example will give False. If you want to accept MIAMI, use the lower case function:
Cells(Row,Column).Value = "Miami"如果单元格完全等于“Miami”，则返回 True。例如，“MIAMI”将给出 False。如果要接受 MIAMI，请使用小写函数：
Lcase(Cells(Row,Column).Value) = "miami"
My suggestions
我的建议
Your sample code keeps changing as you try different suggestions which I find confusing. You were using Cells(Row,Column) <> "Miami"when I started typing this.
当您尝试不同的建议时，您的示例代码不断变化，我觉得这些建议令人困惑。Cells(Row,Column) <> "Miami"当我开始打字时你正在使用。
Use
用
If Cells(i, "A").Value like "*Miami*" And Cells(i, "D").Value like "*Florida*" Then
Cells(i, "C").Value = "BA"
if you want to accept, for example, "South Miami" and "Miami, North".
如果您想接受，例如“南迈阿密”和“迈阿密，北”。
Use
用
If Cells(i, "A").Value = "Miami" And Cells(i, "D").Value like "Florida" Then
Cells(i, "C").Value = "BA"
if you want to accept, exactly, "Miami" and "Florida".
如果你想接受，确切地说，“迈阿密”和“佛罗里达”。
Use
用
If Lcase(Cells(i, "A").Value) = "miami" And _
Lcase(Cells(i, "D").Value) = "florida" Then
Cells(i, "C").Value = "BA"
if you don't care about case.
如果你不在乎大小写。
回答by Christian Specht
If there are no typos in the question, you got the conditions wrong:
如果问题中没有拼写错误，则说明条件错误：
You said this:
你是这么说的：
IF cells (i,"A") contains the text 'Miami'
IF 单元格 (i,"A") 包含文本 'Miami'
...but your code says:
...但你的代码说：
If Cells(i, "A") <> "Miami"
--> <>means that the value of the cell is not equal to"Miami", so you're not checking what you think you are checking.
--><>表示单元格的值不等于“Miami”，因此您没有检查您认为要检查的内容。
I guess you want this instead:
我想你想要这个：
If Cells(i, "A") like "*Miami*"
EDIT:
编辑：
Sorry, but I can't really help you more. As I already said in a comment, I'm no Excel VBA expert.
Normally I would open Excel now and try your code myself, but I don't even haveExcel on any of my machines at home (I use OpenOffice).
抱歉，我真的帮不了你了。正如我在评论中已经说过的，我不是 Excel VBA 专家。
一般情况下我现在打开Excel和尝试你的代码自己，但我甚至不具备的Excel在家里（我使用OpenOffice）我的任何机器。
Just one general thing: can you identify the row that does not work?
Maybe this helps someone else to answer the question.
只有一件事：你能识别出不起作用的行吗？
也许这有助于其他人回答这个问题。
Does it ever execute (or at least try to execute) the Cells(i, "C").Value = "BA"line?
Or is the If Cells(i, "A") like "*Miami*"stuff already False?
If yes, try checking just one cell and see if that works.
它是否曾经执行（或至少尝试执行）该Cells(i, "C").Value = "BA"行？
还是If Cells(i, "A") like "*Miami*"东西已经有了False？
如果是，请尝试只检查一个单元格，看看是否有效。
回答by aevanko
If you are simply looking for the occurrence of "Miami" or "Florida" inside a string (since you put * at both ends), it's probably better to use the InStr function instead of Like. Not only are the results more predictable, but I believe you'll get better performance.
如果您只是在字符串中查找“Miami”或“Florida”的出现（因为您将 * 放在两端），那么使用 InStr 函数而不是 Like 可能更好。不仅结果更可预测，而且我相信你会获得更好的表现。
Also, VBA is not short-circuited so when you use the AND keyword, it will test both sides of the AND, regardless if the first test failed or not. In VBA, it is more optimal to use 2 if-statements in these cases, that way you aren't checking for "Florida" if you don't find "Miami".
此外，VBA 不会短路，因此当您使用 AND 关键字时，它将测试 AND 的两侧，无论第一个测试是否失败。在 VBA 中，在这些情况下最好使用 2 个 if 语句，这样如果找不到“迈阿密”，就不会检查“佛罗里达”。
The other advice I have is that a for-each loop is faster than a for-loop. Using .offset, you can achieve the same thing, but with better effeciency. Of course there are even better ways (like variant arrays), but those will add a layer of complexity not needed in this example.
我的另一个建议是 for-each 循环比 for 循环快。使用 .offset，您可以实现相同的目标，但效率更高。当然，还有更好的方法（例如变体数组），但是这些方法会增加本示例中不需要的复杂性层。
Here is some sample code:
下面是一些示例代码：
Sub test()
Application.ScreenUpdating = False
Dim lastRow As Long
Dim cell As Range
lastRow = Range("A" & Rows.Count).End(xlUp).Row
For Each cell In Range("A1:A" & lastRow)
If InStr(1, cell.Value, "Miami") <> 0 Then
If InStr(1, cell.Offset(, 3).Value, "Florida") <> 0 Then
cell.Offset(, 2).Value = "BA"
End If
End If
Next
Application.ScreenUpdating = True
End Sub
I hope you find some of this helpful, and keep at it with VBA! ^^
我希望您发现其中的一些有用，并继续使用 VBA！^^
回答by rkosegi
I think you should append .value in IF statement:
我认为你应该在 IF 语句中附加 .value ：
If Cells(i, "A").Value <> "Miami" And Cells(i, "D").Value <> "Florida" Then
Cells(i, "C").Value = "BA"
End IF
