need VBA correction from Rick

skyport

Active Member
Joined
Aug 3, 2014
Messages
374
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
8,964
Office Version
2019
Platform
Windows
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

Active Member
Joined
Aug 3, 2014
Messages
374
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

Active Member
Joined
Aug 3, 2014
Messages
374
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

Well-known Member
Joined
Dec 30, 2008
Messages
8,964
Office Version
2019
Platform
Windows
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

Active Member
Joined
Aug 3, 2014
Messages
374
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

Active Member
Joined
Aug 3, 2014
Messages
374
correction to above last sentence- when a key word leads off the first paragraph
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
8,964
Office Version
2019
Platform
Windows
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.

Code:
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
 
Last edited:

skyport

Active Member
Joined
Aug 3, 2014
Messages
374
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,348
Messages
5,443,940
Members
405,256
Latest member
ukboyme

This Week's Hot Topics

Top