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

 

[cmeagan656]

@cabcyvr
Vancouver, BC, Canada? I live within 8 km of cabcyyj.

Cheers.

 

[cabcyvr]

@cmeagan656,

Yes, I am working in Richmond but living in Burnaby, I guess you were living in Delta or NewWest.

Happy New years,

 

[cmeagan656]

Actually YYJ is Victoria Airport. I'm living near Swartz Bay and working in Victoria. When I was younger I used to travel a lot so I know a lot of the airport codes around the world.

Happy New Year to you too!

Cheers.

 

[SkipVought]

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?

Yes.

method 1: Copy the code and paste into a module in the other workbook.

method 2: Drag the module from one VBA Project to another VBA Project.

method 3: Export the module. Import into another VBA Project.

For methods 2 & 3 I would recommend NAMING your module with a unique name.

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

You ought to be able to just enter a new value. I use [TAB] rather than [ENTER] in a List Table, bacause the software will position your active cell to the correct place when you have tables with more than one column. That's ALL you do.



Skip,

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

 

[cabcyvr]

Morning, Skid,

The snow on the road in Vancouver took me 70 minutes to arrive at office this morning, I just tried it by your method, it is perfect.

I really need to read some book to study VBA or basic formula for Excel later. this will definitely help me to better understand the answer from grateful helpers just like you.

Thanks,

Scott

 

[SkipVought]

Been there, so to speak. Was in Boston in 1978 for the blizzard.

First, get to understand Excel's spreadsheet capabilities.

Learn to use the macro recorder to record repetitive tasks. Then learn how to customize that recorded code. There are many good books. I particularly like John Walkenbach's. Check out

http://www.j-walk.com.

Lots of code examples.

Tinker. Try stuff. Browse the VBA forums.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}