Loop lookup copy paste

[Pofski]

Hi,

I am looking for a vb code that would run through column A on the first worksheet, and looks up each reference number in column A of the second worksheet.
After this, if it would find the number in the second list, it should copy the line of information next to the reference number of the second worksheet to the first page.

Thanks

 

[Hui]

Can you upload a sample file?

 

[Pofski]

in my example, the numbers on the sheet "Data" should be completed with the information that can be found on the sheet "Reference".

 

[Marc L]

Hi !

Why a code as a formula can directly do the job ?!
(As even by code often best is a formula way.)

Must see the thread Need VBA Vlookup help …
​

 

[Pofski]

Formula's (which i would personally prefer to use) are not an option in the situation that this file will be used in.
Would also recommend using the index/match lookup in place of vlookup for such things.

 

[Hui]

Copy the following into teh Data Code Module in VBA and run it with F5 or assign a Button to it

Sub Copy_Data()

Dim LR1 As Integer, LR2 As Integer

Sheets("Reference").Activate
LR2 = Range("A" & Rows.Count).End(xlUp).Row

Sheets("Data").Activate
LR1 = Range("A" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False

For i = 2 To LR1
  FindString = CStr(Cells(i, 1))
  With Sheets("Reference").Range("A2:A" + CStr(LR2))
  Set Rng = .Find(What:=FindString, _
  After:=.Cells(.Cells.Count), _
  LookIn:=xlValues, _
  LookAt:=xlWhole, _
  SearchOrder:=xlByRows, _
  SearchDirection:=xlNext, _
  MatchCase:=False)
   
  If Not Rng Is Nothing Then
  Sheets("Reference").Range(Rng.Address).Offset(0, 1).Resize(, 3).Copy
  Range("B" + CStr(i)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  :=False, Transpose:=False
  Else
  Range("B" + CStr(i)) = "Not found"
  End If
  End With
   
Next i

Application.ScreenUpdating = True

End Sub

 

[Marc L]

Formula's (which i would personally prefer to use) are not an option in the situation that this file will be used in.

So see and test Demo1 procedure from link in my previous post !
​

 

[Pofski]

Hui,

thank you for your help.
I managed to adapt your solution to fit completely in our project.

 

[Pofski]

Marc,

thank you for your help as well.
I failed to check out your link, but i will still use it for potential future issue's.

 

[Hui]

As Marc L said above a Formula based solution would be much faster and could be locked down with protection if required