How to use AND in an IF expression

Question

I want to test:

If cells IF (i, "A") contain the text "Miami" and AND (i, "D") contains the text "Florida", then change the 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 

Answer 1

Short syntax lesson

Cells(Row, Column) identifies the cell. The string must be an integer from 1 to the maximum for the version of Excel that you are using. The column must be an identifier (for example: "A", "IV", "XFD") or a number (for example: 1, 256, 16384)

.Cells(Row, Column) identifies the cell inside the sheet identified in the earlier With statement:

 With ActiveSheet : .Cells(Row,Column) : End With 

If you omit the dot, Cells(Row,Column) is inside the active sheet. Therefore wsh = ActiveWorkbook wsh.Range not strictly necessary. However, I always use the With operator, so I don't wonder what sheet I had in mind when I get back to my code after six months. So, I would write:

 With ActiveSheet : .Range. : End With 

Actually, I would not have written above if I really did not want the code to work on the active sheet. What to do if the user has the wrong sheet active when running the macro. I would write:

 With Sheets("xxxx") : .Range. : End With 

because my code only works on xxxx sheet.

Cells(Row,Column) identifies the cell. Cells (rows, columns) .xxxx identify a cell property. 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 some situations, the compiler may be confused, so it is recommended that you enable .Value .

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

Cells(Row,Column).Value = "Miami" will give True if the cell is exactly equal to "Miami". For example, "MIAMI" will give False. If you want to accept MIAMI, use lower case:

 Lcase(Cells(Row,Column).Value) = "miami" 

My suggestions

Your code sample keeps changing as you try to use different sentences that I find confusing. You used Cells(Row,Column) <> "Miami" when I started typing this.

Using

 If Cells(i, "A").Value like "*Miami*" And Cells(i, "D").Value like "*Florida*" Then Cells(i, "C").Value = "BA" 

if you want to take, for example, "South Miami" and "Miami North."

Using

 If Cells(i, "A").Value = "Miami" And Cells(i, "D").Value like "Florida" Then Cells(i, "C").Value = "BA" 

if you want to take exactly Miami and Florida.

Using

 If Lcase(Cells(i, "A").Value) = "miami" And _ Lcase(Cells(i, "D").Value) = "florida" Then Cells(i, "C").Value = "BA" 

if you care.

Answer 2

If there are no typos in the question, you received the wrong conditions:

You said this:

IF (i, "A") cells contain the text "Miami"

... but your code says:

 If Cells(i, "A") <> "Miami" 

-> <> means that the cell value is not equal to "Miami", so you do not check what you think you are checking.

I think you want this instead:

 If Cells(i, "A") like "*Miami*" 

---

EDIT:

Sorry, but I can no longer help you. As I said in the comment, I'm not an expert in Excel VBA.
Usually I opened Excel now and tried my own code, but I don’t even have Excel on any of my computers at home (I use OpenOffice).

Just one general thing: can you define a string that doesn't work?
Maybe this helps someone else answer the question.

Does this (or at least try to execute) execute the string Cells(i, "C").Value = "BA" ? Or already If Cells(i, "A") like "*Miami*" material False ?
If so, try checking only one cell and see if it works.

Answer 3

If you are just looking for the appearance of “Miami” or “Florida” inside the line (since you put * at both ends), it is probably better to use the InStr function instead of Like. The results are not only more predictable, but I believe that you will get better performance.

In addition, VBA does not close, so when you use the AND keyword, it checks both sides of AND, regardless of whether the first test passed or not. In VBA, it is more optimal to use 2 if statements in these cases, so you won’t check Florida unless you find Miami.

My other advice is that the for-each loop runs faster than the for loop. Using .offset, you can achieve the same, but with better efficiency. Of course, there are even better ways (for example, arrays of options), but in this example they will add a layer of complexity.

Here is a 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 these useful tips and continue with VBA! ^^

Answer 4

I think you should add a value in the IF expression:

 If Cells(i, "A").Value <> "Miami" And Cells(i, "D").Value <> "Florida" Then Cells(i, "C").Value = "BA" End IF 

Answer 5

How would you solve this:

If the value from column A is Done or No, and the value from column B is Done or No, place the current timestamp in column D.

Do not replace cells already filled with D, so keep the original timestamp.

I tried many versions, but could not do it correctly.

Could you help me?

many thanks