Insert Data VBA

[alexmb1]

hi guys, this one should be a softball for some of you but it has me stumped. the scenario is this:

I have a worksheet that i open and can select two options from a user from. you can select either 'home' or 'auto' (we are talking about insurance). you can then enter about 6 columns worth of data. now for the harder part.

i have two 'sections' in my worksheet. section one is called 'home' and starts in row 5. section two is called 'auto' and starts on row 35. the first thing i want to do is tell the data to insert in one of the two sections based on what i initially select. so for instance if i select 'auto', the data will insert on row 36 (presumably, row 35 is merged across the active columns and just says 'auto')

now for the second part, eventually the 30 rows i have allotted will fill up and there will come a need to insert new row. i was trying to develop a code that will look into the cell value if you select 'home' and if it says 'auto' it will insert a new row above, thus expanding my 'home' section automatically.

alas, i cannot figure out either of these two things.

any thoughts?

alex

 

[Galaxiom]

I think you would be better off with two separate Sheets in the Workbook.
Moreover most of us here would use Access instead of Excel.

 

[alexmb1]

two separate sheets was my first thought. however, i need multiple sheets for each company that i am profiling. so when it comes to insurance each tab will represent and insurance company. then within the tabs there will be two 'sections' on the sheet. Also, access would be better suited to this but because of the people i am delivering this product to, it is not an option.

 

[Galaxiom]

You have to loop through reading the cells in the column, test them for the string and direct the procedure accordingly. If it hits the lower heading before finding a blank then insert a row.

Alternatively, have you considered using Access and providing users with the free Access runtime? A form can be made to look remarkably like a spreadsheet and you would have the advantage of being able to use subforms for the two sections.

 

[alexmb1]

yes that is exactly what i want to do. my question is how do i do that.

 

[Galaxiom]

Try Google: excel cell loop

One of the 16 million results should get you started.