insert values from one sheet to another

0
votes

I have two sheets

PRINTSCREEN

In sheet1 I have some codes which are repeating. In second sheet I have a list of codes that appears only once and contains,amoung others, the codes from sheets1.

I want to create a function in VBA which search in sheet2 for a value from "code" column from sheet1 and for each code from sheet1 I want to insert into column 4 and 5 the values from sheet2 columns 4 and 5 corresponding to the row found.

I tried something but I'm a beginner:

Dim FindRowNumber As Long
For Each rw In ActiveSheet.Rows
FindRowNumber = Sheet2.Range("A1:F1000").Value.Find(What:=Cells(rw.Row, 1), LookIn:=xlValues)
GetValue1 = Sheet2.Cells(FindRowNumber , 4)
GetValue2 = Sheet2.Cells(FindRowNumber , 5)
Cells(rw.Row, 4).Value =GetValue1 
Cells(rw.Row, 5).Value =GetValue2
Exit For
1
vbaexcel
Can you not just use VLOOKUP? - Tim Wilkinson
Always make sure to explicitly tell VBA what sheets you're using when using Ranges (including .Cells()): FindRowNumber = Sheet2.Range("A1:F1000").Value.Find(What:=Sheet2.Cells(rw.Row, 1), LookIn:=xlValues). - BruceWayne

1 Answers

1
votes

Hey :) I don't think you need VBA for this. You can just use VLOOKUP in column 4 or 5 to pull matching values from sheet 1. Something like =VLOOKUP(A1,Sheet1!$A$1:$A$10,1,FALSE)