need VBA correction from Rick

[skyport]

RE: http://www.mrexcel.com/forum/excel-...-cell-when-key-words-entered.html#post4208838


Rick HELP please. I think we lost ability to connect after last post at the above link. After I tried the program with the test data it was working fine. But now that I am entering data that may be longer or something, the code is giving me a 1004 error formula too long. It seems to work fine on the first 4 lines but if there are more than 4 lines of data, it returns the error. Also see what it does to "Subjective complaints" in the posted example below.

I posted the worksheet and it can be accessed at this link below. Hopefully you can help because it seemed to work so well with the short test data we used.

https://drive.google.com/file/d/0B74...ew?usp=sharing

Also, here was the last code I had on this one:

Sub ReplaceWords()
Dim r As Long, Words As Variant
Words = Sheets("Words").Range("A1", Sheets("Words").Cells(Rows.Count, "A").End(xlUp))
For r = 1 To UBound(Words)
Sheets("Sheet2").Columns("G").Replace Words(r, 1), vbLf & Words(r, 1), xlPart, , False
Next
End Sub

 

[jasonb75]

I don't get runtime error 1004 when running the code on your test sheet.

What it does to "Subjective complaints" would be expected because you also have "Complaints" in the list of words.

If your data has consistency then you could fix it by changing False to True to make the code case sensitive.

 

[skyport]

Jason, thanks for helping. I only get the run time error if there are more than 4 lines and the error says formula too long.

 

[skyport]

After fiddling a bit with it, I now find that it is not related to the number of lines but occurs when there are multiple occasions when the data in different cells begins with one of the key words and in particular those that contain the word Subjective. Also, whenever that word is first in the text, it also appears to do a carriage return leaving a blank line above the text in the cell but not with other cells that do not start with the word Subjective or a key word.

 

[jasonb75]

I can't see that any particular keywords in the cells would cause any problems. It's more likely the number of characters within the cell.

Could you do an =LEN() check on a problem cell to get a character count?

 

[skyport]

The cell count can be quite long and will vary in the future, as many as several pages of data in a given cell.

Another point is that one version we have to run the program on was an older version of Excell from the earlier 2000's however, when i ran it on recent Excel versions on newer machines, it did not give the runtime error but I do note that any time the first paragraph in cell begins with a key word, it drops that paragraph to the next line leaving a blank line with no text.

 

[skyport]

correction to above last sentence- when a key word leads off the first paragraph

 

[jasonb75]

There is your problem, everything has a limit of some kind, versions of excel pre 2007 are considerably lower than more recent ones.

The length of the text in the problem cells is simply exceeding the limits of your older versions of excel.

Regarding the blank line at the beginning of the cells, this should fix it.

Sub ReplaceWords()
Dim r As Long, Words As Variant
Words = Sheets("Words").Range("A1", Sheets("Words").Cells(Rows.Count, "A").End(xlUp))
For r = 1 To UBound(Words)
    Sheets("Sheet2").Columns("G").Replace " " & Words(r, 1), vbLf & Words(r, 1), xlPart, , True
Next
End Sub

 

[skyport]

That seemed to resolve the final issue and all is well. Thanks again for the great help. People like yourself here on Mr. Excel make the world a better place for your efforts.