Wrong line in the macro.

[k0st4din]

Hi to all.
Friends, colleague helped me an awful lot, but obviously very busy and not enough time to care for me to fix an error in this macro.
My request to you is to help me and look at why it gives me an error on this line.
I emphasize: you only need to fix the error, because I've already put in over 30 workbooks.
Can you look at it and help me get better?
Thank you very much in advance.

Sub DoReplacements()  Dim X As Long, Cell As Range, CellText As String, WS As Worksheet
  Dim Words As Variant, Replacements As Variant
  Const TableSheetName As String = "Sheet2"
  Application.Volatile
  Words = Sheets(TableSheetName).Range("L1", Cells(Rows.Count, "L").End(xlUp))
  Replacements = Sheets(TableSheetName).Range("N1", Cells(Rows.Count, "N").End(xlUp))
  For Each WS In Worksheets
    For Each Cell In WS.Range("H1", Cells(Rows.Count, "H").End(xlUp))
      CellText = ""
      For X = 1 To UBound(Words)
        If InStr(1, Cell.Value, Words(X, 1), vbTextCompare) Then CellText = CellText & "+" & Replacements(X, 1)
      Next
      Cell.Offset(, 6).Value = Mid(CellText, 2)
    Next
  Next
End Sub

[lecxe]

Hi

I did not have time to test the code, but I see a syntax error in the second For Each statement:

Try:

For Each Cell In WS.Range("H1", WS.Cells(Rows.Count, "H").End(xlUp))

[lecxe]

... and the same error in 2 other statements before:

    Words = Sheets(TableSheetName).Range("L1", Sheets(TableSheetName).Cells(Rows.Count, "L").End(xlUp)) 
    Replacements = Sheets(TableSheetName).Range("N1", Sheets(TableSheetName).Cells(Rows.Count, "N").End(xlUp))

Otherwise it would only work if Sheet2 was the active sheet when the code runs

[k0st4din]

Many, many, many thanks.
That's exactly what I was looking for.
Golden man you are.
Be alive and well.
Bow to before you.

[lecxe]

I'm glad it's working OK. Cheers!

[k0st4din]

Hello everyone ,
we all know that at work arise any obstacles and a change associated with some thing.
Already helped me a lot with the repair of that macro and now works incredibly
But now I would like to ask is there a way back to less rework this macro so that searching for a specific thing ( word, number or word and number) , you ask why:
Should be the same macro, but to add something to look - >> exactly what is written in my database , but not close to the desired word .
In sheet 2 is my base data, in sheet 3 (but here I show how it is now(P) and how it should be(Q) ) and everyone sheets else.
I really very urgent and if there is anything you do not understand with my request , please contact me so I can answer you .
And another thing: I can not understand why only some of the search terms shows me an error , and others that are pretty close do not show?