Could someone tell me why the code below is not working. This is what I want it to do.
If a cell in column D says emilio pucci or max mara or tom ford and also if it sees the word sunglasses in column H within the same row then replace whats in the cell in column E to Marcolin and if it says gucci or saint laurent or balenciaga and also if it sees the word sunglasses in column H then replace whats in the cell in column E to Kering
Example: D3 has Emilio Pucci and in H3 says sunglasses then in E3 to replace whats in there to the Marcolin.
Right now it is not doing anything.
Sub ReplaceBrandNames()
Dim ws As Worksheet
Dim lastRow As Long, i As Long
Dim brandName As String
' Set the worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name
' Find the last row in column D
lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
' Loop through each row in column D
For i = 2 To lastRow
' Check if the cell in column D contains one of the specified brand names
brandName = ws.Cells(i, "D").Value
If brandName = "emilio pucci" Or brandName = "max mara" Or brandName = "tom ford" Or brandName = "roberto cavalli" Or brandName = "bally" Or brandName = "max mara" Or brandName = "moncler" Then
' Check if the corresponding cell in column H contains the word "sunglasses"
If InStr(1, ws.Cells(i, "H").Value, "sunglasses", vbTextCompare) > 0 Then
' Replace the value in column E with "Marcolin"
ws.Cells(i, "E").Value = "Marcolin"
End If
ElseIf brandName = "gucci" Or brandName = "saint laurent" Or brandName = "balenciaga" Or brandName = "stella mccartney" Or brandName = "reike nen" Or brandName = "Alaïa" Or brandName = "courreges" Or brandName = "bottega veneta" Or brandName = "tomas maier" Or brandName = "mcq alexander mcqueen" Then
' Check if the corresponding cell in column H contains the word "sunglasses"
If InStr(1, ws.Cells(i, "H").Value, "sunglasses", vbTextCompare) > 0 Then
' Replace the value in column E with "Kering"
ws.Cells(i, "E").Value = "Kering"
End If
ElseIf brandName = "isabel marant" Or brandName = "carrera" Or brandName = "givenchy" Or brandName = "rag & bone" Or brandName = "dior" Or brandName = "fendi" Then
' Check if the corresponding cell in column H contains the word "sunglasses"
If InStr(1, ws.Cells(i, "H").Value, "sunglasses", vbTextCompare) > 0 Then
' Replace the value in column E with "Safilo Group"
ws.Cells(i, "E").Value = "Safilo Group"
End If
End If
Next i
MsgBox "Replace Text Completed"
End Sub