VLOOKUP, but causes result to stay permanently

[mharper90]

Perhaps this is wishful thinking, but I'm looking for something that retrieves data similarly to a VLOOKUP function, but the resulting value would then become permanent.

My example:

I have a list of names on ws2, and an identical list of names on ws1, plus some extra outliers on ws1 that will never be on ws2. I want to use the name in column A on ws2, to find the matching name in column B of ws1, and then return particular values from the matching row on ws1 to the same row on ws2, but I want to decide which cells go where (such as ws1 column E would go to ws2 column C). I understand how to do this with VLOOKUP, but I only want this to happen on command, with a macro. So I don't want every change to ws1 to automatically update on ws2 as it would with a VLOOKUP. I just want it to update when and if the macro is run. I'd want this to run for every name in the list on ws2.

 

[jmacleary]

Hi there.

You could record a macro creating the vlookups in whatever cells you want the results, then finally select all the cells, copy then paster special... values. This will do the lookup but then replace the formulas with just the values.

Hope this helps.

 

[JazzSP8]

It's a bit clumsy perhaps, but a starting point for you if nothing else;

Sub CopyAcross()

Application.ScreenUpdating = False

Sheets("ws2").Select
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For x = 2 To LastRow
    NameToFind = Cells(x, "A").Value

    Sheets("ws1").Select
    Set SearchRange = Range("B1", Range("B" & Rows.Count).End(xlUp))
    Set findrow = SearchRange.Find(NameToFind, LookIn:=xlValues, lookat:=xlWhole)
    
    If Not findrow Is Nothing Then
        
        Sheets("ws2").Cells(x, "B").Value = Cells(findrow.Row, "D").Value
        Sheets("ws2").Cells(x, "C").Value = Cells(findrow.Row, "C").Value
    
    End If

    Sheets("ws2").Select

Next x

Application.ScreenUpdating = True

End Sub