Loop: If Vlookup <> cell value then clear cell.

[countryfan_nt]

Hello friends, hope all is well!

Please help me edit/fix the below code:
It is supposed to go through A2 & below till The cell is blank (Looping A2 to blank cell)

The code has a vlookup; I wish to have an IF statement: if vlookup result <> the cell value in B2 & below, then the clear contents of A2 & below.
if the lookup matches go to next cell of A.

Your kind help is needed and really appreciated in advance.

Sub test()
 Dim x As String
 Sheets("sheet1").Select
 Range("A2").Select
Do
If x = Application.WorksheetFunction.VLookup(ActiveCell.Value, Worksheets("sheet1").Range("J4:K11"), 2, False) <> ActiveCell.Offset(0, 1) Then
 ActiveCell.ClearConents
 Else
 
 ActiveCell.Value = x
 End If
 ActiveCell.Offset(1, 0).Activate
 Loop Until IsEmpty(ActiveCell.Value)
 End Sub

 

[gallen]

Your if statement was a little confused. I haven't tested your syntax on your Vlookup but this corrects the if statement:

Sub test()
Dim x As String
Sheets("sheet1").Select
Range("A2").Select
    Do
        x = Application.WorksheetFunction.VLookup(ActiveCell.Value, Worksheets("sheet1").Range("J4:K11"), 2, False)
        If x <> ActiveCell.Offset(0, 1) Then
            ActiveCell.ClearConents
        Else
            ActiveCell.Value = x
        End If
        ActiveCell.Offset(1, 0).Activate
    Loop Until IsEmpty(ActiveCell.Value)
End Sub

 

[DanteAmor]

Hello friends, hope all is well!

Please help me edit/fix the below code:
It is supposed to go through A2 & below till The cell is blank (Looping A2 to blank cell)

The code has a vlookup; I wish to have an IF statement: if vlookup result <> the cell value in B2 & below, then the clear contents of A2 & below.
if the lookup matches go to next cell of A.

Your kind help is needed and really appreciated in advance.

Sub test()
 Dim x As String
 Sheets("sheet1").Select
 Range("A2").Select
Do
If x = Application.WorksheetFunction.VLookup(ActiveCell.Value, Worksheets("sheet1").Range("J4:K11"), 2, False) <> ActiveCell.Offset(0, 1) Then
ActiveCell.ClearConents 'This is a mistake, Clearcontents is not an Activecell method
 Else
 
 ActiveCell.Value = x 'if the lookup matches go to next cell of A, Then you should not change the value of your cell.
 End If
 ActiveCell.Offset(1, 0).Activate
 Loop Until IsEmpty(ActiveCell.Value)
 End Sub

I wish to have an IF statement: if vlookup result <> B2, then clear contents of A2, if matches go to next cell of A.

According to the above, it could be like this:

Sub test1()
    Dim c As Range, f As Range
    Sheets("sheet1").Select
    For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
        Set f = Range("J4:K11").Find(c.Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not f Is Nothing Then
            If f.Offset(1).Value <> c.Offset(1).Value Then c.ClearContents
        End If
    Next
End Sub