VBA VLookup to replace certain values only

[lopiteaux]

Hi all - I'm a bit stuck with a fairly basic VLookup and was hoping someone could help. In short, what I want the VLookup to do is go down a list of values in column P, perform a VLookup using the primary key in column A on a seperate sheet in the workbook, and replace ONLY those values for which it finds the key.

The code I have at the moment returns the value if found and populates with "TEST IGNORE" if not found... I cannot seem to get the code to just skip to the next cell WITHOUT changing the value in the active cell...

Sub UpdateValues()
    
    Range("P1").Select
      
    Do
        ActiveCell.Value = Application.VLookup(ActiveCell.Offset(0, -15), Sheets("LOOKUPSOURCE").Columns("A:F"), 6, False)
        If IsError(ActiveCell.Value) = True Then
            ActiveCell.Value = "TEST IGNORE"
            ActiveCell.Offset(1, 0).Select
        Else
            ActiveCell.Value = Application.VLookup(ActiveCell.Offset(0, -15), Sheets("LOOKUPSOURCE").Columns("A:F"), 6, False)
            ActiveCell.Offset(1, 0).Select
        End If
    Loop Until IsEmpty(ActiveCell.Offset(0, -15).Value)
    
End Sub

Any ideas?

Thanks!

 

[markmzz]

Could you explain better what you want?

Markmzz

 

[markmzz]

I think this code below is what you want, if not tell us.

Sub UpdateValues()
    'Definition of variables of the macro
    Dim myCell As Range
    Dim myLookup
 
    'Navigates by cells of the column P until the corresponding
    'cell (in the same row) of the column is empty
    For Each myCell In Sheets("VLOOKUP").Range("P:P")
        'If the corresponding cell in column A is empty Exit For
        If IsEmpty(myCell.Offset(0, -15).Value) Then
            Exit For
        End If
        'Ignores errors encountered
        On Error Resume Next
        'Stores the result of the Vlookup function in
        'the variable myLookup
        myLookup = WorksheetFunction.VLookup(myCell.Offset(0, -15), _
            Sheets("LOOKUPSOURCE").Columns("A:F"), 6, False)
        'If no error occurred running the Vlookup function
        'Stores the value found in the corresponding cell in column P
        If Err = 0 Then
            myCell.Value = myLookup
        Else
            'If an error occurs clears all property settings of the
            'Err object for the next myCell
            Err.Clear
        End If
        'Active error checking
        On Error GoTo 0
    Next myCell
End Sub

Markmzz

 

[Jonmo1]

Take full advantage of VBA's Powers...

Use Variables to store values, instead of putting them in a cell.
This way, you only do the vlookup once (not twice)
Assign it's result to a variable.
Then you test the variable for error
If not an error, put the value of the variable in the cell.

Try this

Sub UpdateValues()
Dim lr As Long, i As Long
Dim x As Variant
lr = Range("A1").End(xlDown).Row
For i = 1 to lr
    x = Application.Vlookup(Cells(i,"A"),Sheets("LOOKUPSOURCE").Columns("A:F"), 6, False)
    If Not(Iserror(x)) Then
        Cells(i,"P").Value = x
    End If
Next i
End Sub

 

[markmzz]

Another solution:

Sub UpdateValuesV2()
    'Definition of variables of the macro
    Dim myCell As Range
    Dim myLookup
 
    'Navigates by cells of the column P until the corresponding
    'cell (in the same row) of the column is empty
    For Each myCell In Sheets("VLOOKUP").Range("P:P")
        'If the corresponding cell in column A is empty Exit For
        If IsEmpty(myCell.Offset(0, -15).Value) Then
            Exit For
        End If
        'Stores the result of the Vlookup function in
        'the variable myLookup
        myLookup = Application.VLookup(myCell.Offset(0, -15), _
            Sheets("LOOKUPSOURCE").Columns("A:F"), 6, False)
        'If no error occurred running the Vlookup function
        'Stores the value found in the corresponding cell in column P
        If Not IsError(myLookup) Then
            myCell.Value = myLookup
        End If
    Next myCell
End Sub

 

[lopiteaux]

jonmo1, you're a genius. works like a charm, and clean as well! i'll remember this one...

thanks for all the help guys

l