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

countryfan_nt

Well-known Member
Joined
May 19, 2004
Messages
688
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.

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

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
1,996
Your if statement was a little confused. I haven't tested your syntax on your Vlookup but this corrects the if statement:

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

Well-known Member
Joined
Dec 3, 2018
Messages
12,202
Office Version
2007
Platform
Windows
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.

Rich (BB code):
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:

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

Watch MrExcel Video

Forum statistics

Threads
1,102,454
Messages
5,486,996
Members
407,575
Latest member
calc

This Week's Hot Topics

Top