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

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

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

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   amp  Rows Count  End xlUp  Row  For Each cell In Range  A1 A   amp  lastRow      If InStr 1  cell Value   Miami    lt  gt  0 Then         If InStr 1  cell Offset   3  Value   Florida    lt  gt  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

[excel] How to use AND in IF Statement

Examples related to excel

Examples related to vba