compare first 2 letters of cell and clear if not there

[bmwbuyer2007]

Hello All,

Here's one for you!!!

I have information in rows b2:b3567

and i have information in rows d2:d375

I would like to compare the first 3 letters of each line of data in d2:d375 against the first 3 letters in b2:b3567 and clear the contents of any within b2:b3567 if the first 3 letters do not match the first 3 letters in d2:d375.

if that makes sense !?!?

please help!!!

Cord.

 

[Lewiy]

Do you mean to check the first 3 letters of each cell in B against every cell in D and clear it if there is no match at all?

 

[bmwbuyer2007]

Lewiy!!!

How are you?

yes you're right compare every cell but only clear the data in cells b2:b3567 if there is no match within the first 3 letters against d2:d375

 

[Lewiy]

Ok, try this:

Sub MyMacro()
Application.ScreenUpdating = False
Dim b As Long
Dim d As Long
Dim flag
For b = 2 To 3567
    flag = False
    For d = 2 To 375
        If Left(Cells(b, 2), 3) = Left(Cells(d, 4), 3) Then
            flag = True
        End If
    Next d
    If flag = True Then
        Cells(b, 2).ClearContents
    End If
Next b
Application.ScreenUpdating = True
End Sub

 

[bmwbuyer2007]

that worked to remove the ones which did match, so i changed your flag = true statement to flag = false and hey presto !!!

thanks again lewiy, you trully are a credit to this forum!!!