copy an entire excel case if contains a specific word.

[rhaedan77]

Hello !

I'm a French teacher, and I need your help to manage an excel document.

I have downloaded thousands of sentences in French with their english translations that I stored in an excel sheet. In another sheet, when I write a word in French in A1, I would like the case B1 to show automatically the French sentences with the matching word, and the case C1 to show the english translation.

For exemple :

Here is my first sheet with the sentences.

Je serai bientôt de retour.	I will be back soon.
C’était un méchant lapin.	That was an evil bunny.

In the second sheet, I will just write the word :

bientôt
lapin

And I would like the french exemple + the translation to pop up automatically.

bientôt	Je serai bientôt de retour.	I will be back soon.
lapin	C’était un méchant lapin.	That was an evil bunny.

Is there any a way to do that ?

Sorry for my crappy english and my poor excel skills

 

[ER_Neha]

Hello, Do you want a vba coding as a solution?

 

[rhaedan77]

Hello, Do you want a vba coding as a solution?

If there is no other alternative, it would be great
Thank you

 

[ER_Neha]

Hello Please Try this Once and Provide your feedback.
also change the sheet name accrding to your sheet name

Sub mycode()

Dim i As Integer
Dim j As Integer

Dim lastrow1 As Integer
Dim lastrow2 As Integer

lastrow1 = Worksheets("Sheet1").Range("a1").CurrentRegion.Rows.Count
lastrow2 = Worksheets("Sheet2").Range("a1").CurrentRegion.Rows.Count

For i = 1 To lastrow2
    For j = 1 To lastrow1
        If IsNumeric(VBA.InStr(1, Worksheets("sheet2").Range("a" & i).Value, _
                        Worksheets("sheet1").Range("a" & j).Value, vbTextCompare)) Then
            Worksheets("sheet2").Range("b" & i).Value = Worksheets("sheet1").Range("b" & j).Value
            Exit For
        End If
    Next j
Next i
End Sub

 

[rhaedan77]

Sub mycode() Dim i As Integer Dim j As Integer Dim lastrow1 As Integer Dim lastrow2 As Integer lastrow1 = Worksheets("Sheet1").Range("a1").CurrentRegion.Rows.Count lastrow2 = Worksheets("Sheet2").Range("a1").CurrentRegion.Rows.Count For i = 1 To lastrow2 For j = 1 To lastrow1 If IsNumeric(VBA.InStr(1, Worksheets("sheet2").Range("a" & i).Value, _ Worksheets("sheet1").Range("a" & j).Value, vbTextCompare)) Then Worksheets("sheet2").Range("b" & i).Value = Worksheets("sheet1").Range("b" & j).Value Exit For End If Next j Next i End Sub

Thanks a lot for your help.

After having past the code, it is said that the "capacity is overflowed".

 

[ER_Neha]

Try this once

Sub mycode()

Dim i As Integer
Dim j As Integer

Dim lastrow1 As Byte
Dim lastrow2 As Byte

lastrow1 = Worksheets("Sheet1").Range("a1").CurrentRegion.Rows.Count
lastrow2 = Worksheets("Sheet2").Range("a1").CurrentRegion.Rows.Count

For i = 1 To lastrow2
    For j = 1 To lastrow1
        If IsNumeric(VBA.InStr(1, Worksheets("sheet2").Range("a" & i).Value, _
                        Worksheets("sheet1").Range("a" & j).Value, vbTextCompare)) Then
            Worksheets("sheet2").Range("b" & i).Value = Worksheets("sheet1").Range("b" & j).Value
            Exit For
        End If
    Next j
Next i
End Sub

 

[rhaedan77]

Sub mycode() Dim i As Integer Dim j As Integer Dim lastrow1 As Byte Dim lastrow2 As Byte lastrow1 = Worksheets("Sheet1").Range("a1").CurrentRegion.Rows.Count lastrow2 = Worksheets("Sheet2").Range("a1").CurrentRegion.Rows.Count For i = 1 To lastrow2 For j = 1 To lastrow1 If IsNumeric(VBA.InStr(1, Worksheets("sheet2").Range("a" & i).Value, _ Worksheets("sheet1").Range("a" & j).Value, vbTextCompare)) Then Worksheets("sheet2").Range("b" & i).Value = Worksheets("sheet1").Range("b" & j).Value Exit For End If Next j Next i End Sub

I have the same message

 

[Fluff]

Change both Integer & Byte to Long

 

[rhaedan77]

Change both Integer & Byte to Long

Thanks for your answer.
When I'm doing it, there is only the english translation of my first sentence that appears for every words I write.

Like :

homme	When he asked who had broken the window, all the boys put on an air of innocence.
manger	When he asked who had broken the window, all the boys put on an air of innocence.
saison	When he asked who had broken the window, all the boys put on an air of innocence.
froid	When he asked who had broken the window, all the boys put on an air of innocence.

Also, I have to let you know that there might be some duplicates words in the thousands of sentences I have. I just need one sentence and it translation to show up, not all the sentences that contain that word.