add a new line above a certain "keyword"
 

Hello.
My sheet looks like this:
A B C D....

start Header
--- Text&Formulars
--- Text&Formulars
--- Text&Formulars
end
start Header
--- Text&Formulars
--- Text&Formulars
--- Text&Formulars
end

I wrote a makro which ads a new line&copies the Text&Formulars in th
new line. Works fine, but I'd like to change it to make it mor
comfortable:

When a cell between the lines "start" and "end" is selected the ne
line with Text&Formulars should always be added above "end". Afte
that, the first cell in the new line should be selected.

I played around with ".End(xlToRight)" but it seem's that's not th
right way...

Thank you for any help.
Stefa

--
skolle
-----------------------------------------------------------------------
skoller's Profile: http://www.excelforum.com/member.php...fo&userid=3238
View this thread: http://www.excelforum.com/showthread.php?threadid=52365

add a new line above a certain "keyword"
 

sub Justfortest
dim rng as range, cell as range
Dim rng1 as Range
set cell = cells(activecell.row,1)
if instr(1,cell,"end",vbTextcompare) then
set rng1 = cell
else
set rng = Range(cell,cells(rows.count,1).End(xlup))
set rng1 = rng.Find(What:="end", _
After:=cell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if rng.row <= cell.row then
set rng1 = rng(rng.count)(2)
end if
end if
rng1.select
msgbox rng1.address
End sub

Doesn't consider situation where end is not found in Column A.

--
Regards,
Tom Ogilvy

"skoller" wrote:


Hello.
My sheet looks like this:
A B C D....

start Header
--- Text&Formulars
--- Text&Formulars
--- Text&Formulars
end
start Header
--- Text&Formulars
--- Text&Formulars
--- Text&Formulars
end

I wrote a makro which ads a new line&copies the Text&Formulars in the
new line. Works fine, but I'd like to change it to make it more
comfortable:

When a cell between the lines "start" and "end" is selected the new
line with Text&Formulars should always be added above "end". After
that, the first cell in the new line should be selected.

I played around with ".End(xlToRight)" but it seem's that's not the
right way...

Thank you for any help.
Stefan


--
skoller
------------------------------------------------------------------------
skoller's Profile: http://www.excelforum.com/member.php...o&userid=32388
View this thread: http://www.excelforum.com/showthread...hreadid=523653