Question: I have a workbook with 2 sheets.  

Sheet1 contains a list of Product Codes in column A and Column R is Current Stock Level.
Sheet2 contains a list of Product Codes in column A and Column B contains the New Stock Level.
What I want to do is replace the Current Stock Levels in Sheet1 with the New Stock Level from Sheet2.

I found some code on this site already (below) which I have adapted slightly for my purpose and it works fine but only for one Product Code (as it references A1 and B1). What I would like to do is add a Loop so it works down all products in Sheet2 but I'm not sure how to and haven't been able to adapt any similar loops I've found online for this purpose.

Any help would be appreciated, my backup plan is to just do a v-lookup in Sheet1 to bring in the Sheet2 New Stock Level values and then replace the original column but I would like to get this other way working if possible.

```
Private Sub CommandButton1_Click()

    Dim search_range As Range, search_value As Range, _
    lastcell As Range, foundcell As Range
    Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("Sheet1")
Set search_range = ws.Range("A1", ws.Range("A" & Rows.Count).End(xlUp))
Set lastcell = search_range.Cells(search_range.Cells.Count)
Set search_value = ThisWorkbook.Sheets("Sheet2").Range("A1")

Set foundcell = search_range.Find(What:=search_value, After:=lastcell, 
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not foundcell Is Nothing Then foundcell.Activate Else MsgBox "Not Found"

ActiveCell.Offset(0, 17).Value = Sheets("Sheet2").Range("B1").Value

End Sub

```

Answer: 
How about the following:

```
Private Sub CommandButton1_Click()

Dim search_range As Range, search_value As Range, lastcell As Range, foundcell As Range
Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("Sheet1")
Set search_range = ws.Range("A1", ws.Range("A" & Rows.Count).End(xlUp))
Set lastcell = search_range.Cells(search_range.Cells.Count)

For i = 1 To lastcell.Row

    Set search_value = ThisWorkbook.Sheets("Sheet2").Range("A" & i)
    Set foundcell = search_range.Find(What:=search_value, After:=lastcell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)

    If Not foundcell Is Nothing Then foundcell.Activate Else MsgBox "Not Found"
    ActiveCell.Offset(0, 17).Value = Sheets("Sheet2").Range("B" & i).Value

Next i

End Sub

```
The idea is the following - you have two types of ranges - ranges where you search and ranges where your value should be. They are called `Target` and `Search`. 

In the code below you loop through all cells in column `A` of the first worksheets and you look for their value in column `A` of the second worksheet. If you find the value, you write the value in column `B` of the second worksheet to the 17. column in the first worksheet:

```
Private Sub CommandButton1_Click()

    Dim targetRange     As Range
    Dim targetValue     As Range
    Dim searchRange     As Range        
    Dim lastSearchCell  As Range
    Dim foundCell       As Range        
    Dim wsTarget        As Worksheet
    Dim wsSearch        As Worksheet

    Dim myCell          As Range

    Set wsTarget = ThisWorkbook.Worksheets(1)
    Set wsSearch = ThisWorkbook.Worksheets(2)

    With wsTarget
        Set targetRange = .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
    End With

    With wsSearch
        Set searchRange = .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
    End With

    Set lastSearchCell = searchRange.Cells(searchRange.Cells.Count)

    For Each myCell In targetRange
        Set foundCell = searchRange.Find(What:=myCell, After:=lastSearchCell).Offset(0, 1)
        If Not foundCell Is Nothing Then
            myCell.Offset(0, 17) = foundCell
        Else
            MsgBox "Not Found"
        End If
    Next myCell

End Sub

```