find a cell word and put thier meaning to next cell

[Rakesh Kumar]

Please help by accept my challange
i have two excels
1)Paragraph.xls
2)dictionary.xls

paragraph.xls contains sentences in cell range A1:A3000
dictionary.xls contains two row first having words and second having their meaning(like A1=add and B1=to join something)
can i run a macro in paragraph.xls to match and pick all the words meaning in B1:B3000 which contain in dictionary.xls to the paragraph.xls

Example:-
Paragraph.xls
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]A1[/TD]
[TD]To Add a Product[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]To delete a product[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]To Add and delete a Product[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Dictionary.xls
[TABLE="width: 500"]
<tbody>[TR]
[TD]A1[/TD]
[TD]Add[/TD]
[TD]To Join[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]Delete[/TD]
[TD]To Remove[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]Product[/TD]
[TD]After impliment raw goods[/TD]
[/TR]
</tbody>[/TABLE]

After running macro in Paragraph.xls output will be
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]A1[/TD]
[TD]To Add a Product[/TD]
[TD]Add >To Join
Product >After impliment raw goods[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]To delete a product[/TD]
[TD]Delete >To Remove
Product >After impliment raw goods[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]To Add and delete a Product[/TD]
[TD]Add >To Join
Delete >To Remove
Product >After impliment raw goods[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[JLGWhiz]

this procedure assumes the data is contained in sheet 1 in both workbooks. Edit the sheet reference in the code by replacing the index number with the actual sheet name, enclosed in parentheses, ie. Sheets("data")

Sub knowWhatIMean()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet, lr As Long, c As Range, wd As Variant, fn As Range
Set wb1 = Workbooks("Paragraph.xls") 'Edit
Set wb2 = Workbooks("dictionary.xls") 'Edit
Set sh1 = wb1.Sheets(1) 'Edit sheet name
Set sh2 = wb2.Sheets(1) 'Edit sheet name
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
    For Each c In Range("A2:A" & lr)
        wd = Split(c.Value, " ")
        For i = LBound(wd) To UBound(wd)
            If Application.CountIf(sh2.Range("A:A"), wd(i)) > 0 Then
                Set fn = sh2.Range("A:A").Find(wd(i), , xlValues)
                c.Offset(0, 1) = c.Offset(0, 1) & wd(i) & ": " & fn.Offset(0, 1).Value & Chr(10)
                Set fn = Nothing
            End If
        Next
    Next
End Sub

the code should be copied to the standard code module 1. See instructions at bottom of post for accessing the code module.

 

[Rakesh Kumar]

Many Thanks for reply!
Its confusing me because i am not brilliant as you
Can you please upload here a single macro enable excel contain sheet1= paragraph and sheet2= dictionary
Many thanks

 

[Rakesh Kumar]

Great Works Many Thank to you! its working

 

[Rakesh Kumar]

but macro not pick the word containing space in dictionary (like:- united states of america is not pick by macro)
if any code to check dictionary words in paragraph excel than it will be possible