如何在IF语句中使用AND和VBA [英] How to use AND in IF Statement - VBA

问题描述

任何人都可以告诉我这里的代码在哪里。我应该添加另一个如果语句？

基本上它应该检查：

如果单元格（i，A）包含文字Miami AND 在（我，D）中包含文本Florida THEN 将单元格（i，C）的值更改为BA。

  Sub ABC（）
 Dim wsh As Worksheet，As As Long，lngEndRowInv As Long 
 Set wsh = ActiveSheet 
 
i = 2 
 lngEndRowInv = wsh.Range（A& wsh.Rows.Count）.End（xlUp）.Row 
虽然i <= lngEndRowInv 
如果像*迈阿密一样，像* Miami *和Cells（i，D）这样的单元格（i，A）就像* Florida *那么
 Cells（i，C）Value = BA
 End If 
i = i + 1 
 Wend 
 End Sub 
  

解决方案

简短语法课

行，列）标识单元格。行必须是1和您正在使用的Excel版本的最大值之间的整数。列必须是标识符（例如：A，IV，XFD）或数字（例如：1，256，16384）

code> .Cells（Row，Column）标识在早期With语句中标识的表单中的单元格：

 使用ActiveSheet 
：
 .Cells（行，列）
：
结束
  

如果省略点，单元格（行，列）在活动工作表中。所以 wsh = ActiveWorkbook wsh.Range 并不是绝对必要的。但是，我总是使用With语句，所以我不知道在六个月内返回到我的代码时，我的意思是什么。所以，我会写：

 使用ActiveSheet 
：
 .Range。 
：
结束
  

其实我不会写上面的，除非我真的希望代码在活动工作表上工作。如果用户在启动宏时有错误的表单活动，该怎么办？我会写：

  With Sheets（xxxx）
：
 .Range。 
：
结束
  

因为我的代码只适用于工作表xxxx。 / p>

单元格（行，列）标识单元格。单元格（行，列）.xxxx标识单元格的属性。 值是属性。 Value是默认属性，所以你通常可以省略它，编译器会知道你的意思。但是在某些情况下，编译器可能会被困惑，所以建议包括 .Value 是好的。

如果单元格是迈阿密，南迈阿密，迈阿密，北或类似的东西，则*迈阿密*的单元格（行，列）将为True。 p>

单元格（行，列）.Value =迈阿密如果单元格完全等于迈阿密。例如MIAMI会给False。如果你想接受MIAMI，请使用小写字母函数：

  Lcase（Cells（Row，Column）.Value）=我的建议 
 
 
 
您的示例代码不断变化，因为您尝试不同的建议，我觉得困惑。您正在使用 Cells（Row，Column）<> 迈阿密当我开始输入这个。
 
 
 
使用
像*迈阿密*和细胞（i，D）等值，如*佛罗里达*，然后
细胞（我，A C）Value =BA
  
如果你想接受，例如南迈阿密和迈阿密，北。
 
 
 
使用
 如果单元格（i，A）Value =MiamiAnd Cells（i，D）值如Florida然后
 Cells（i，C）Value =BA
  
如果你想接受迈阿密和佛罗里达。
 
 
 
使用
 如果Lcase（Cells（i，A）。Value）=miami和$ 
 Lcase（Cells（i，D）。Value）=florida然后
单元格（i，C）。Value =BA
  
如果您不在乎案例。
 
Can anyone tell me where am I going wrong in this code here. Should I add another If statement?

Basically, it should check:

IF cells (i,"A") contains the text 'Miami' AND in (i,"D") contains the text 'Florida' THEN change value of cell (i,"C") to 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

解决方案
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) identifies a cell within a sheet identified in a earlier With statement:
With ActiveSheet
  :
  .Cells(Row,Column)
  :
End With
If you omit the dot, Cells(Row,Column) is within the active worksheet.  So wsh = ActiveWorkbook wsh.Range is 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:
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.

Cells(Row,Column) identifies a cell.  Cells(Row,Column).xxxx identifies a property of the cell.  Value is 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 .Value is good.

Cells(Row,Column) like "*Miami*" will give True if the cell is "Miami", "South Miami", "Miami, North" or anything similar.

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:
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.

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.

                        
这篇关于如何在IF语句中使用AND和VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！