Insert row(s) with vba

[Andy Bailey]

I'd appreciate some help to determine if I'm attempting the impossible...

We have a workbook to generate proposals. Each proposal contains numerous
sections. Each section has many line items. The first item in section 1 is
numbered 01-001, the next 01-002 etc. The 01 is the section number, the 001
is the item number. Iuse the formula:-
=CONCATENATE((TEXT($A$2,"00")),"-",(TEXT((RIGHT(OFFSET(A5,-1,0),3)+1),"000")))
to automatically generate the item number row by row. The value in $A$2 is
the section number. This way I can use vba to insert a row and the numbers
look after themselves.

If we're lucky enough to get the order I'd like to use the proposal workbook
as a basis for executing the contract. Now for the problem. Each item in the
proposal could consist of a number of separate sub items which we'd then
identify as 01-001-001, 01-001-002 etc to allow us to order all the bits.

What I'm trying to do is generate some vba to insert a row (or a number of
rows) that allows me to detail the sub items but not lose the original
numbering.

Something like this:-

Proposal
Item No. Sub-item Name
01-001 Widget A
01-002 Widget B

Now a contract - use vba to insert rows where necessary
Item No. Sub-item Name
01-001 Widget A
01-001 001 Sub part a1
01-001 002 Sub part a2
01-002 Widget B
01-002 001 Sub part b1
01-002 002 Sub part b2
01-002 003 Sub part b3

I think I've rambled enough. Any help appreciated.

Andy

 

[Don Guillett]

You can make a macro starting from the bottom up

if cells(i-1,1)<>cells(i,1) then rows(i).resize(2).insert

 

[Andy Bailey]

Don,

Appreciate your help. I tried to implement your suggestion but didn't get
very far. I'm not sure I explained my problem too well.

What I'm after is some way to enter a new row but get the formula references
to ignore the fact that I've entered a new row and "respect" their original
references.

Thanks

Andy

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.