Find Value in another sheet, Copy offset (old sheet), paste offset (New Sheet)

0
votes

I wonder if you can help me build a VBA script that does the following:

Searched for a value taken from column C in sheet1, and finds it in column C sheet2, copy value in column B sheet1 (offset) and pastes it to the same row it found the value C to column B (offset)

Keep in mind that the data are ordered the same row on both sides, therefor i need Find function.

It needs to go in loop untill last row is done in sheet1 (searched all rows and copied value from sheet1, and paste value to sheet2)

Thank you so much for your help

3
excelvba
You don't need a code for this, you could achieve that with the functions MATCH and INDIRECT. – Damian
Problem is that these cointain 750k of line so this will take forever or? – Jonas Korani
@Damian, did you mean MATCH and INDEX ? – user4039065
Correct, didnt think about it as a solution, but that would work i guess can you help me make it, it needs to loop thro all rows in sheet 1 tho – Jonas Korani
mmmm my excel is in spanish, so it's a bit challenging keep it up with the english versions. I tried on my excel and i used MATCH, INDIRECT and ADDRESS. If vlookup would work with negative columns it'd be easier but... – Damian

3 Answers

0
votes

Sheet1

Sheet2

As you can see Sheet1 contains some comments, while sheet2 does not, also sheet2 have more rows with some different values.

I want to be able to make the program locate the Item1 Column in Sheet2 and add to it the comments from sheet1, to sheet2 comments

0
votes

With this formula on column b from sheet2 should work

=INDIRECT(ADDRESS(MATCH(C2;Sheet1!$C:$C);2;1;1;"Sheet1"))
0
votes

I want to thank you for the contribution, i managed to make it work finally. I found this script (sry dont remember who owns it, and modified it to my needs)

Sub test()


Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim oCell As Range


Dim i As Long
i = 2

Set ws1 = ThisWorkbook.Sheets("Data New")
Set ws2 = ThisWorkbook.Sheets("Mellomlagring")

Do While ws1.Cells(i, 1).Value <> ""
    Set oCell = ws2.Range("H:H").Find(What:=ws1.Cells(i, 8))
    If Not oCell Is Nothing Then ws1.Cells(i, 2) = oCell.Offset(0, -6)
    i = i + 1
Loop

Set ws1 = Nothing
Set ws2 = Nothing
End Sub