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.

 

[FormR]

I wonder if you already have line feeds in the text, try this small amendment to mine:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range, w As Range
Application.EnableEvents = False
Set r = Intersect(Target, Columns("G"))
If Not r Is Nothing Then
    For Each c In r
        c.Replace vbLf, " ", LookAt:=xlPart
        With Sheets("Words")
            For Each w In .Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
                If InStr(1, c.Value, w.Value, vbTextCompare) > 1 Then
                    c.Replace w.Value, vbLf & w.Value, LookAt:=xlPart
                End If
            Next w
        End With
        c.EntireRow.AutoFit
    Next c
End If
Application.EnableEvents = True
End Sub

 

[skyport]

when trying the Macro by Rick, it gives an error 1004 and debug highlights the following line:

Words = Sheets("Words").Range("A1", Cells(Rows.Count, "A").End(xlUp))

 

[skyport]

FormR

Ran second version and it treats it the same as the first.

 

[FormR]

If you copy the example text you gave directly from post#7 in the forum and paste it into column G does it work as expected?

It does for me.

 

[skyport]

Just tried again and it comes out as below:


Subjective
Complaints: Persistent pain in neck bilaterally with headaches radiating numbness and tingling symptoms in upper extremity. Low back pain is 50% improved since radiofrequency ablation treatment.Current
Medications: Vicoprofen, Relafen and Prilosec.
Diagnoses: Neck pain-minor discogenic and changes in small spur complexes. Low back pain, disc disease at L5-S1, mild bilateral foraminal narrowing. No stenosis. Abnormal electrodiagnostic studies, nerve conduction study was positive for left mild carpal tunnel syndrome, but otherwise normal.
Plan: It is not believe that carpal tunnel syndrome is what is bothering her the most. She will continue her current
Medications.


Work status: Remains unchanged

 

[FormR]

Can you put the workbook on a file sharing site, like dropbox for example and share the link here?

 

[skyport]

I want to thank you for sticking with this. I put it on Google Docs and you should be able access it with this link It has both yours and Rick's Macro in it.

https://drive.google.com/file/d/0B749X0hxm6l8Mkl2cVRQODNGZ1E/view?usp=sharing

 

[skyport]

One other very important thing that is happening that would also cause a problem is that the text that remains in the cell after running the code leaves a lot of blank space above the text in the cell and also does not allow for later editing to manually insert a line of new text above the existing text.

 

[Rick Rothstein]

when trying the Macro by Rick, it gives an error 1004 and debug highlights the following line:

Words = Sheets("Words").Range("A1", Sheets("Words").Cells(Rows.Count, "A").End(xlUp))

There was a mistake in that line of code... see what I added in red to correct it.

 

[skyport]

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