Find string in Excel sheet from Word doc

[user_v1]

Hi,
i have a macro that reads a selection in a (open) word file
and i want to find this selection in the 2nd column of a worksheet
and copy the cell value of the 4th column (same row).
I've tried several methods with no success.
I ask for your help for the [find] section and the selection of the cell in 4th column.

This reads all cells in the 2nd column but doesn't find the string from Word (r) that exists in 10th row.


With wb.Sheets("new_prices").Range("b1") '<--------------------- wb is the open excel file
For idx = 1 To .CurrentRegion.Rows.Count
With Selection.Find
.Forward = True
.ClearFormatting
.MatchWholeWord = False
.MatchCase = False
.Wrap = wdFindContinue
.Text = r '<-------------------------------------- string from Word
If .Execute = True Then
..............................
..............................
End If
End With
Next
End With

Thanks

[gmayor]

If I understand the requirement correctly, you want to search column 2 of your worksheet for a term selected in a word document, and if that term is found, you want to copy the value from column 4 of the same row to column 2?
That being the case the following Word macro should do the job:


Sub Macro1()
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Dim idx As Long
Dim wdDoc As Document
Dim oRng As Range

On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err Then
MsgBox "Open Excel and the workbook first!", vbCritical
GoTo lbl_Exit
End If
On Error GoTo 0
Set xlWB = xlApp.activeworkbook
Set wdDoc = ActiveDocument
Set oRng = Selection.Range
If Len(oRng) = 0 Then
MsgBox "Nothing selected in the document!", vbCritical
GoTo lbl_Exit
End If

Set xlWS = xlWB.Sheets("new_prices")
With xlWS
For idx = 1 To .UsedRange.Rows.Count
Debug.Print .Cells(idx, 2) & vbTab & oRng.Text
If .Cells(idx, 2) = oRng.Text Then .Cells(idx, 2) = .Cells(idx, 4)
Next idx
End With
lbl_Exit:
Set wdDoc = Nothing
Set oRng = Nothing
Set xlApp = Nothing
Set xlWB = Nothing
Set xlWS = Nothing
Exit Sub
End Sub

[user_v1]

Thank you very much for your answer.
I didn't mention the selection term is part of cell's content. Finally with this lines i have the desired result.



.
.
With xlWS
For idx = 1 To .UsedRange.Rows.Count
If InStr(.Cells(idx, 2).Value, oRng) <> 0 Then
.Cells(idx, 4).Copy
GoTo 30
End If
Next idx
MsgBox "Code not found"
GoTo lbl_Exit
End With
.
.