How to use AND in IF Statement

Question

I want to check   IF cells  i  A   contains the text  Miami  AND  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   amp  wsh Rows Count  End xlUp  Row While i  lt   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

User · Answer

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.

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

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.

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! ^^

User · Answer

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   lt  gt   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

User · Answer

If there are no typos in the question  you got the conditions wrong   You said this      IF cells  i  A   contains the text  Miami       but your code says   If Cells i   A    lt  gt   Miami    --   lt  gt  means that the value of the cell is not equal to  Miami   so you re not checking what you think you are checking   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 have Excel on any of my machines at home  I use 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

User · Answer

I think you should append  value in IF statement   If Cells i   A   Value  lt  gt   Miami  And Cells i   D   Value  lt  gt   Florida  Then     Cells i   C   Value    BA  End IF

[excel] How to use AND in IF Statement

The answer is

Examples related to excel

Examples related to vba

Tags