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
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