Loop with values in Column D in Sheet 2 to find Values anywhere on Sheet 3 and Paste to adjacent Cell E in Sheet 1

[Timjan]

Hi dear Members,

The syntax for creating a loop to use values in Column D in Sheet 2 to find Values anywhere on Sheet 3 and Paste to adjacent Cell E in Sheet 1 eludes me.

Could anyone point me in the right direction, please?

Thanks

 

[Timjan]

This Code below is what I have at the moment but it only returns the correct Value in Sheet 2 Column E. Why does it not do the remainder Cells in Column D in Sheet 2?

Sub findCellVal()
Dim c As Range
Sheet1.Activate
Dim ra As Range
Dim lr As Long

lr = Sheet2.Cells(Rows.Count, 4).End(xlUp).Row
Set ra = Sheet3.Range("A:D")
 
For i = 2 To lr
    Sheet3.Activate

    Set ra = Cells.Find(what:=Sheet3.Range("D2"), LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)

    If ra Is Nothing Then
        MsgBox "not found"
        Else
        Sheet2.Range("E2") = ra.Value
    End If
Next i
End Sub

I know it is a simple fix, but I just can not see the wood from the trees right now.

 

[DanteAmor]

Forget the code for a moment, can you explain with examples what you want to do?

 

[Timjan]

Hi DanteAmor,

I will try to give you some "Screen Shots"

I can not do Screen Shots.

This is Sheet 2

A	B	C	D	E
1			Search value	Result
2			Apple	Apple
3			Banana	Banana
4			Cherry	Cherry
5			Orange	Orange
6			Avo	Avo
7			Peanuts	Peanuts

<colgroup><col style="mso-width-source:userset;mso-width-alt:2596;width:53pt" width="71"> <col style="mso-width-source:userset;mso-width-alt:2560;width:53pt" width="70"> <col style="mso-width-source:userset;mso-width-alt:2998;width:62pt" width="82"> <col style="mso-width-source:userset;mso-width-alt:3693;width:76pt" width="101"> <col style="mso-width-source:userset;mso-width-alt:2633;width:54pt" width="72"> </colgroup><tbody>
</tbody>

This is Sheet 3 A B C D E 1 2 3 Apple 4 Peanuts 5 Avo 6 Orange 7 Banana 8 9 10 Cherry 11 12 <tbody> </tbody>
The aim is to use the data in Column D in Sheet2 to find that same value in sheet 3 and paste it to Column E in Sheet 2. HTH

<tbody>
</tbody>

 

[DanteAmor]

Try this

Sub findCellVal()
  Dim c As Range, f As Range
  For Each c In Sheet2.Range("D2", Sheet2.Range("D" & Rows.Count).End(xlUp))
    Set f = Sheet3.Cells.Find(c, , xlValues, xlWhole)
    If Not f Is Nothing Then
      c.Offset(, 1).Value = c.Value
    Else
      c.Offset(, 1).Value = "Not found"
    End If
  Next
End Sub

 

[Timjan]

DanteAmor,

Thank you so much. It is a hit!

Would it be possible to add the Cell Address of the Values found in sheet 3 to say Column F in sheet 2?

Bowled you with this one! LOL

 

[DanteAmor]

Would it be possible to add the Cell Address of the Values found in sheet 3 to say Column F in sheet 2?

After this line

c.Offset(, 1).Value = c.Value

Add this line

c.Offset(, 2).Value = c.Address

 

[Timjan]

Hi DanteAmor,

Thank you for your time and help.

c.Offset(, 2).Value = c.Address

Returns the Cells address of column D in Sheet2, instead of the respective Cell Addresses in Sheet3 where the values were found.

 

[DanteAmor]

Sorry

Try this

c.Offset(, 2).Value = [B]f[/B].Address

 

[Timjan]

Hi DanteAmor,

Bingo!!! You are a star! Absolutely BRILLIANT!

Thank you very much.