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

		Apple	Apple
		Banana	Banana
		Cherry	Cherry
		Orange	Orange
		Avo	Avo
		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>
[TD="class: xl67, width: 71"]A[/TD]
[TD="class: xl67, width: 70"]B[/TD]
[TD="class: xl67, width: 82"]C[/TD]
[TD="class: xl67, width: 101"]D[/TD]
[TD="class: xl67, width: 72"]E[/TD]

[TD="class: xl66"]1[/TD]

[TD="class: xl65, width: 101"]Search value[/TD]
[TD="class: xl65, width: 72"]Result[/TD]

[TD="class: xl66"]2[/TD]

[TD="class: xl66"]3[/TD]

[TD="class: xl66"]4[/TD]

[TD="class: xl66"]5[/TD]

[TD="class: xl66"]6[/TD]

[TD="class: xl66"]7[/TD]

</tbody>

This is Sheet 3 <tbody> [TD="class: xl66"]A
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>

	Apple
	Peanuts
		Avo
			Orange
	Banana

[TD="class: xl66, width: 72"]B[/TD]
[TD="class: xl66, width: 74"]C[/TD]
[TD="class: xl66, width: 64"]D[/TD]
[TD="class: xl66, width: 64"]E[/TD]
[TD="width: 64"][/TD]

[TD="class: xl66"]1[/TD]

[TD="class: xl66"]2[/TD]

[TD="class: xl66"]3[/TD]

[TD="class: xl66"]4[/TD]

[TD="class: xl66"]5[/TD]

[TD="class: xl66"]6[/TD]

[TD="class: xl66"]7[/TD]

[TD="class: xl66"]8[/TD]

[TD="class: xl66"]9[/TD]

[TD="class: xl66"]10[/TD]

[TD="class: xl65, colspan: 2"]Cherry[/TD]

[TD="class: xl66"]11[/TD]

[TD="class: xl66"]12[/TD]

</tbody>

[/TD]

</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.