Loop: If Vlookup <> cell value then clear cell.
Results 1 to 3 of 3

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

  1. #1
    Board Regular countryfan_nt's Avatar
    Join Date
    May 2004
    Location
    Saudi Arabia
    Posts
    651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Board Regular gallen's Avatar
    Join Date
    Jun 2011
    Location
    Manchester UK
    Posts
    1,851
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

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

    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

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,213
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

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

    Quote Originally Posted by countryfan_nt View Post
    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 '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
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •