PDA

View Full Version : Wrong line in the macro.



k0st4din
02-26-2014, 10:01 AM
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
02-26-2014, 10:22 AM
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
02-26-2014, 10:27 AM
... 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
02-26-2014, 11:27 AM
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
02-26-2014, 02:01 PM
I'm glad it's working OK. Cheers!

k0st4din
03-02-2014, 12:16 PM
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?