Excel 2003:How to insert line break at designate position in cell 3

[cabcyvr]

Hi,

I am using Excel 2003 at office, are there any experts to teach me how to use Marco or VBA to automatically insert line before and after designated word in each cell under the same column.

Example.
Under the column A, there are Cell A1 to A100, each cell has the same word of “Interval” but it exists in different description. (Whatever they show, I want to use “Interval” and “Interval:”as my keyword to make formula or Macro)

A1: (Original)
Change engine oil.Interval: 5000 KM.
A2: (Original)
Lubricate axle joint.Interval: 3000 Hours.




I want to: (note: * = line break)
-Insert 2 lines (line break) before “Interval” and
-Insert 1 line after “Interval:” and
-automatically search the last position of the content in A1 to A100, and insert 2 lines after it

A1:
=======================
Change engine oil.*
*
Interval:*
5000 KM.*
*
=======================
A2:
=======================
Lubricate axle joint.*
*
Interval:*
3000 Hours.*
*
=======================


Thank you very much for help,

Happy New Year !

Scott

In Canada

 

[DaveInIowa]

I'm not sure if I'd be able to persuade Marco to automatically insert the lines either , but here's some code that might work:

Public Sub thread68_1521718()
    Dim rg As Range
    Dim newValue As String
    
    newValue = vbLf & vbLf & "Interval:" & vbLf
    
    Set rg = ThisWorkbook.Worksheets(1).Range("A1")
    Do While (rg.Value > "")
    
        rg.Value = Replace(rg.Value, "Interval: ", newValue)
        rg.Value = rg.Value & vbLf
    
        Set rg = rg.Offset(1)
    Loop
    
End Sub

 

[SkipVought]

You could use a formula in another cell...
[tt]
=LEFT(A1,FIND("Interval",A1)-1)&CHAR(10)&CHAR(10)&MID(A1,FIND("Interval",A1),9)&CHAR(10)&RIGHT(A1,LEN(A1)-(FIND("Interval",A1)+9))&CHAR(10)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cabcyvr]

Thanks you so much, DaveInIowa and SkipVought, the method from both of you work great !

For the method provided by SkipVought, I don't quite understand the meaning of number at left side of ")&",but once I made a try to change it,I got the secret.

Acutually, I am not a expert to use formula or Macro in Excel at all even I know it will provide great help to improve work efficiency. I had bought a study book to study it based on my real time questions.

Thanks again for your help. This is really a good place to come.



Happy New Year,

Scott

In Richmond,BC,Canada

 

[cabcyvr]

Hi, here is my continued question, could you please help again.


A1: (Original)
Visual inspection of tire. Interval: daily
A2: (Original)
Lubricate axle joint.Interval: 3000 Hours. Threshold note:7000 KM





I want to: (note: * = line break)
--Insert 2 lines (line break) before “Interval” and
--Insert 1 line after “Interval:” and
--If the cell doesn’t have “Threshold note”(example:A1),automatically search the last position of the content in cell and insert 3 lines after it
--If the cell has “Threshold note”(example:A2),automatically search the last position of the content in cell (before “Threshold note” , and insert 2 lines after it (or insert 2 lines before “Threshold note”)) and insert 3 lines after the last position (for the description of threshold note)

A1:
=======================
Visual inspection of tire.*
*
Interval:*
daily.*
*
*
=======================

A2:
=======================
Lubricate axle joint.*
*
Interval:*
3000 Hours.*
*
Threshold note:*
7000 KM*
*
*

Thanks,


Scott

 

[SkipVought]

What other strings will you be adding to the mix?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

1. Make a table of ALL the strings you want to identify
[tt]
Val
Interval:
Threshold note:
[/tt]
Use this modified code from Dave

Public Sub thread68_1521718()
    Dim rg As Range, r As Range
    Dim newValue As String
    
    Set rg = ThisWorkbook.Worksheets(1).Range("A1")
    rg.Offset(1).Value = rg.Value
    Set rg = rg.Offset(1)
    
    For Each r In Sheet1.[Val]
        newValue = vbLf & vbLf & r.Value & vbLf
        
        rg.Value = Replace(rg.Value, r.Value, newValue)
        rg.Value = rg.Value & vbLf
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cabcyvr]

Happy New Year,

Could you please modify the code from Dave for me, I am not so smart to understand what you are saying. I did make several times of try to replace the key word inside but it doesn’t work.

I made excel spreadsheet but I can't get it directly uploaded to this server.


//////Source file:////// (Excel 2003)
Task 1.Visual inspection of tire.Interval: daily
Task 2.Lubricate axle joint.Interval: 3000 Hours.Threshold note:7000 KM
Task 3.Operational check-Battery.


//////Requirement://////
note:
Please make it work at the specified worksheet in a workbook)

A. If task has "Interval" note only,Example: Task 1

1). Insert 2 line break before "Interval:"
2). Insert 1 line break after "Interval:"
3). Insert 3 line break after the last word of "interval" description.

B. If task has both Interval and threshold note,example Task 2

1). Insert 2 line break before "Interval:"
2). Insert 1 line break after "Interval:"
3). Insert 2 line break before "Threshold note:"
4). Insert 1 line break after "Threshold note:"
5). Insert 3 line break after the last word of "Threshold note:" description.

C. If task doesn't have any additional note.example Task 3
1). Insert 3 line break after the last position of task description (whatever they are)


//////Additional request://////
In this example, the keyword to be used are "Interval:" and "Threshold note:". Could you please let me know where to go to change it to other "keyword" or "Val" as required in the Marco Code ?
I mean if I want to use or add other key words to make the simlilar change, how should I do ? For example, if I want to add "Applicability Note" as new key word.


Thanks again,

Scott

 

[SkipVought]

How do you identify "... additional note..."?

BTW, I failed to state that Val is a Named Range defined using Insert > Name > Create names in top row or Insert > Name > Define

"if I want to use or add other key words to make the simlilar change, how should I do "

Just ADD that name to your list (mine is named Val). If you use the Data > List > Create list feature and then define your Named range, as you add values to your list, the Named Range will dynamically adjust.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cabcyvr]

Hi, Skid,

"additional note" in my example means the task only has the description for itself, there is no other specified requirement. Task 3 is example, it doesn't have "inerval note","threshold note" or other knid of "note".


I am doing the test by your method now.

thanks,

scott

 

[SkipVought]

Please post an example that includes an "additional note"

Please note that you can define EXACTLY where "Interval:" and "Threshold note:" begin.

How do you define EXACTLY where an "additional note" begins?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

"I made excel spreadsheet ..."

Did you desing and enter the data into this workbook? Do you have control over the content?

If so, YOU can decide what string constitues an "additional note"!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cabcyvr]

Hi,Skid,

I think it is my fault that I still didn't say my request clearly.

example: Task 2.Lubricate axle joint.Interval: 3000 Hours.Threshold note:7000 KM.
Based on basic "task description"(Lubricate axle joint) "Additional note" in this example is "Interval: 3000 Hours.Threshold note:7000 KM. ".

I had created a temp email for you, please enter and see the spread sheet in detail. the file was saved in attach of the email in "drafts" folder inside.

qlistofexcel@gmail.com password: excelhelp

Thanks,

Scott

 

[SkipVought]

example: Task 2.Lubricate axle joint.Interval: 3000 Hours.Threshold note:7000 KM.
Based on basic "task description"(Lubricate axle joint) "Additional note" in this example is "Interval: 3000 Hours.Threshold note:7000 KM. ".

I think it is my fault that I still didn't say my request clearly.

Yes!!! Your latest "example" is EXTREMELY confusing!!! {Interval"[/b] and Threshold note: are NOT Additional note Are you stating that additional note is a GENERAL definition, that does not connote the same specificity as the items in the Val list?

Please define ALL of your terms clearly, completely and concisely.

Back to the last question I asked in post 2 Jan 09 17:26. Please answer.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cabcyvr]

Hi,Skid,

"Are you stating that additional note is a GENERAL definition, that does not connote the same specificity as the items in the Val list?"

A: Yes, You are correct.

"Did you desing and enter the data into this workbook? Do you have control over the content?"

A:
1).Yes, I designed and enter data into it.(pls see the file in email).
2). I only built the spreadsheet and made data entry with the question and request starting from blank page.

Thanks,

Scott

 

[cabcyvr]

Skid, I will leave office now, and thank your for your help to my question.

Have a good weekend.

Scott

 

[SkipVought]

There is NO FILE in your link. It is ONLY an Email Address.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cmeagan656]

@Skip
It looks like what cabcyvr has done is create a gmail account and created an email with the file attached. If you log in to Gmail using qlistofexcel@gmail.com as the user name and excelhelp as the password and click on the "drafts" folder you will find a draft email with the file attached.

My guess is that cabcyvr didn't know how else to upload the file for you to look at. The file is there (I checked) but Excel is definitely NOT my strong point.

Cheers.

 

[SkipVought]

I changed the macro to a user defined function (UDF)

Public Function thread68_1521718(s As String)
    Dim r As Range
    Dim newValue As String
    
    thread68_1521718 = s
    
    For Each r In Sheet1.[Val]
        newValue = vbLf & vbLf & r.Value & vbLf
        
        thread68_1521718 = Replace(thread68_1521718, r.Value, newValue)
    Next
    thread68_1521718 = thread68_1521718 & vbLf
    thread68_1521718 = thread68_1521718 & vbLf
End Function

posted the list on a seperate sheet and named the range.

used the UDF in column C

attached the workbook in your draft.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cabcyvr]

Hi,Skid,

I had got the attach file from the email, that is exactly what I want. Please forgive my unclear request and thanks for the time you spent for me.

Per your reply, you said you had changed Macro to UDF.

1. May I know if I could copy this function to make it working at another workbook? If Yes, could you please let me know how?

2. How could I add the new "value" of "Access note:" in "Val" list setup by you?

Val
Interval:
Threshold note:
Access Note:

I entered something with "Access note:" in B6 and used "Data > List > Create list" to add "Access Note" in "Val", but display in C6 wasn't changed accordingly. Did I do something wrong again??

Skid, I checked your profile at this website before I left office today, it is so glad to know you are working at Aerospace. The reason why I want to use the code is to modify the format of data in excel file converted from Boeing MPD, then use it to build customized Maintenance schedule for different customers. Last time, I was told by other people to use “Substitute” formula to get it changed, but it is not the best way with the most convenience.

Thanks for your time,

Scott


Val
Interval:
Threshold note:
Access Note:

I entered something in B6 and used "Data > List > Create list" to add "Access Note" in "Val", but display in C6 wasn't changed accordingly. Did I do something wrong again??

Skid, I checked your profile at this website before I left office today, it is so glad to know are working at Aerospace. The reason why I want to use the code is to modify the format of Boeing MPD after it was converted to xls from PDF, then use it to build customized Maintenance schecdule for different customers.

Thanks,

Scott