Begin a new line in cell when key words are entered

[skyport]

What I'm hoping to have is a code that will say have a list of precise key words in column A on a page to be named "words" so that new ones could be added from time to time.

On page 2 of the work book, in column G, text would be entered into cells by either typing or pasting existing text from another source. Each time a key word from the word page is encountered in a cell in column G on page 2 from the text pasted or typed, it would take that key word and the text that follows and make it begin on the next line WITHIN that cell.

EXAMPLE:

Say the words list contained the key words Impression: and Diagnosis:


The data entered in a single cell in column G may look like this when typed or pasted:

The person involved had the doctor review the accident. Impression: The complaint was shoulder pain. Diagnosis: Shoulder sprain.

That same data when the code is ran would look like the following in the single cell:

The person involved had the doctor review the accident.
Impression: The complaint was shoulder pain.
Diagnosis: Shoulder sprain.


Any help would be much appreciated.

 

[Rick Rothstein]

Thanks Rick, I really did want to try BOTH concepts. I am assuming yours goes into a regular module.

Yes, although looking at the sheet you posted, I'm not 100% sure my column references are correct, so you may want to check that out.

 

[FormR]

In your list of words:

Shouldn't cell A7 be "Subjective Complaints:" not "Complaints:"?

And you have "Work Status:" in both cell A4 and A13 - shouldn't it only be in one or the other?

 

[skyport]

To Both FormR & Rick


I figured I would address this one to both of you at the same time with answers to each of your questions because both concepts really could work well in different ways that would be very advantageous, which is why I'm so interested in both of them working and it also helps us all learn as we go. I will thank both of you again for your efforts.


Regarding the question concerning complaints should be subjective complaints in cell A7 on the words page, you actually caught an error I made of omission which I've since added because there should be both complaints by itself as well as subjective complaints each with a colon after. Rick, I believe you have the column references correct because the program works virtually hundred percent with one very interesting issue, which just happens to seem to be related to the question that FormR raised about the keyword phrase "work status" being twice on the word list. The reason that word phrase was in there twice was as an experiment on my side to see if a second posting of the word phrase would eliminate a problem that was occurring in both codes that I found totally unexplained and unable to figure out. It would seem that the word "Work" triggers off something that creates that line as well as the following lines to skip a line and moved downward and it happened on both codes. Please note in the examples below three different versions or attempts which are explained as follows: Example number one shows what happens when the work status line is at the end of the paragraph or cell and notice the gap between the last word after the plan line and then before the work status line (all that blank space). In my attempts to try to figure this, in example number two I switched the work status line up into the middle of the data and note what it did was simply put the space in the middle of the data instead of at the end as it did in example 1. This led me to try example number three where I simply substituted the word work for the word play making it play status instead of work status in the original text that was entered plus of course adding that as a new keyword phrase. Amazingly there appears to be something about the word "work" that's causing this space gap because as you can see in example 3 the problem no longer exist and the result is perfect. I had initially thought that there might of been a corruption or something causing the problem from my entry of the word phrase "work status" so I did a trial with entering a new one which was the second entry and in my trial I had eliminated the first one. It did not seem to make any difference the strange error still occurred. See below to visually see how the three examples turned out as explained above:

[h=1]Example 1[/h][TABLE="width: 467"]
<tbody>[TR]
[TD]Chief complaint: He has pain in the elbow and also the right hand and does not feel that this is caused by anything else but where he has been working for the past several months.
Diagnosis: Left shoulder pain and sprain.
Medications: Vicodin 25 mg, Naprosyn and Lisinopril.
Plan: Stay off work, do exercises and take
vacation..

Work status: Remain off work for two months


[h=1]Example 2[/h][TABLE="width: 467"]
<tbody>[TR]
[TD]Chief complaint: He has pain in the elbow and also the right hand and does not feel that this is caused by anything else but where he has been working for the past several months.
Diagnosis: Left shoulder pain and sprain.

Work status: Remain off work for two months.
Medications: Vicodin 25 mg, Naprosyn and Lisinopril.
Plan: Stay off work, do exercises and take
vacation.


[h=1]Example 3[/h]Chief complaint: He has pain in the elbow and also the right hand and does not feel that this is caused by anything else but where he has been working for the past several months.
Diagnosis: Left shoulder pain and sprain.
Play Status: Remain off work for two months.
Medications: Vicodin 25 mg, Naprosyn and Lisinopril.
Plan: Stay off work, do exercises and take
Medications.



[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[skyport]

SUCCESS ALERT

I now have Ricks version working perfectly. For everyones info, a trace of FormR's code must have been left on the template I used to try Rick's. Therein created a conflict beween the 2 even though I had eliminated one code before trying the other. The FormR code does still leave the gap when the Work status line is involved as illustrated in previous comment. That seemed to affect the running of Rick's even though i had eliminated the first code before trying the second. When rerunning each on completely different computers, Rick's functions perfectly. The only issue remaining with the FormR code is the spacing as illustrate in Examples: 1&2 in previous post. I will be happy to continue to analyze any adjustments you have to get both working. Thanks so much for both of you helping.

 

[Rick Rothstein]

SUCCESS ALERT

I now have Ricks version working perfectly. For everyones info, a trace of FormR's code must have been left on the template I used to try Rick's.

Great! I was writing a response a few moments ago when my browser lost its connection and I had trouble getting back on (we are having a rather loud thunderstorm right now)... in it, I was advising you to remove FormR's code as well as the second occurrence of "Work status:" because when I did that, my code worked fine for me. However, there are a couple of issues that need to be addressed. First, my macro needs to be revised...

[SIZE=1]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[B][COLOR="#0000FF"], , False[/COLOR][/B]  Next
End Sub[/SIZE]

The revision is to make the Replace method guaranteed to be case sensitive. The reason this needs to be done is so some of your shorter words won't conflict with longer "words" containing the shorter one at the end. For example, "Complaints:" and "Chief complaints:". If Replace is not made case sensitive, and if both of these words are in the cell, the word "Complaints:" would be processed inside of "Chief complaints:" Next, I note that the last two items in the list ("Medications" and "Current Medications") are missing the trailing colon that every other word in the list has... without them, the Replace method call could accidentally process the word "Medications" inside the body of the text. Now, once you put the colons on the end, you will need to make the word "Medications" inside "Current Medications" lower case for the reason stated earlier for "complaints".

 

[skyport]

Rick, Brilliant observation and one I noticed but had now overlooked. I first saw it on the medications word. Thank for picking that up.

 

[skyport]

Rick with new code I get a compile / syntax error this line highlighted in module:
Sheets("Sheet2").Columns("G").Replace Words(r, 1), vbLf & Words(r, 1), xlPart, , False Next

Although this did not seem to affect the previous version, Please consider that just before the macro is run there is another one that adds a carriage line of space above the text for future info to be added above the text. See below for reference on that one. Again, it had no affect on the previous version. Maybe the two can be merged or incorporated together

Sub MyMacroCode()


Dim myLastRow As Long
Dim myRow As Long


Application.ScreenUpdating = False

' Find last row in column G
myLastRow = Cells(Rows.Count, "G").End(xlUp).Row

' Loop through all cells in column G
For myRow = 1 To myLastRow
' Check to see if there is a value in column G...
If Cells(myRow, "G") <> "" Then
' ...if so, insert soft carriage return
Cells(myRow, "G") = Chr(10) & Cells(myRow, "G")
End If
Next myRow

Application.ScreenUpdating = True

End Sub

 

[Rick Rothstein]

Rick with new code I get a compile / syntax error this line highlighted in module:
Sheets("Sheet2").Columns("G").Replace Words(r, 1), vbLf & Words(r, 1), xlPart, , False Next

The word "Next" accidentally got sucked up from the line below when I edited the code. Move it from the end of that code line to the line below (so it can close off the For statement) and all should be well.

 

[skyport]

BINGO - BULLS EYE. That did it. Also solved the other macro issue at same time. Your's now does both all in one. A true Rothstein Masterpiece. . Keep this up and I'm going to forced to fly to Jersey just to buy you and your family dinner (humor). Thanks yet again for the wonderful solutions. You all make this forum a God send.

 

[skyport]

Rick HELP please. 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.

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/0B749X0hxm6l8VHFrTHZ2TnMzSTA/view?usp=sharing