Copy and insert with relative reference to formulas

**[email protected]** · 08-27-2012, 10:46 PM

I am trying copy multiple rows of data and insert it between every existing row of data in my sheet.

You can see this in the attached sheet. Relative Formula Copy and Insert.xlsm

Row 3 and Row 4 should be copied and inserted between rows 5 and 6, 6 and 7, 7 and 8, etc.

This must copy the cell formulas not the values. It must also copy the whole row not just the first column.

I'm trying to get the following macro to copy and insert but the inserted formula is exactly as it was copied and I need it to be relative. As in change the formula based on where the formula is inserted.
Currently if the formula in the cell refers to L2 the formula copied also contain L2.

I need the formula to change the reference so it matches with the row above it. Another words if the cell is inserted 10 rows below where it was copied it needs to reference L12 instead of L2.

I think this is called relative reference.

Im not sure how to change this so that it maintains relative reference.

Please Login or Register  to view this content.

Thank you for any help!

**MarvinP** · 08-27-2012, 11:12 PM

Hey,

Do the way I've done in the attached. It keeps your data in a table and simply better. No VBA needed.

**[email protected]** · 08-27-2012, 11:46 PM

The trouble with this way is that i need to use this document to import my products into a shopping cart. In order to do this i must build out rules for each product in the format as I had shown.

The shopping cart will not accept an import of product rules in a tabular format.

For this reason i still require my original question.

In the real document there are 20,000 products so a VBA solution rather than manual solution is required.

Thank you for you time and help, i do appreciate it. Maybe i should have been more clear as to what needs to be achieved with this. Sorry.

Please can anyone help with my original question?

**MarvinP** · 08-27-2012, 11:58 PM

Hi,

Your formula of Sum( Stuff * stuff * 12) didn't need a "Sum". There was a single thing to sum so the "sum" was useless.

If you keep the data like I've suggested it will be a TABLE of data and work with Excel much easier. I'd need to see your data as it is imported to see why inserting two rows makes sense. I just can't give good answers if it makes the data or problem harder in the long run. That is why I gave the answer above.

**[email protected]** · 08-28-2012, 12:15 AM

Alright, sorry, the document im working with has all sorts of pricing information in it that i cant post on the internet.

I have gone through and made some numbers up so dont try and make sense of it cause it wont make sense.

What you will see is a better picture of what we need to do. The formulas, as you will see, are nothing like the sample document i gave and quite complex.

In terms of why it is formatted the way it is... that question will need to be asked to the shopping cart company who tells you how to import data. It unfortunately is not going to change any time soon.

Each of the products you see there need to have the same rules applier to it and the shopping cart reads it from top to bottom. Product 35, then the rules that apply to product 35. Then it goes on to product 36 and the rules for 36, product 37, etc...

I really believe a vba solution is what is required here.

Relative Formula Copy and Insert Sample Data.xlsm

Thanks for all the help either way!

**MarvinP** · 08-28-2012, 12:20 AM

Hi,

I'd still try to keep the answer on the same row as the data. VBA with inserted rows will simply make the answer and problem a lot harder and less useful. In my humble opinion.

Maybe one of the smart guru's can work their magic on this problem.

**[email protected]** · 08-28-2012, 12:35 AM

ya it may make sense but not if i want to actually import this stuff into my shopping cart rather than mess with this just for the fun of it!

what i do think would be good is to store this info in columns as you suggest in addition to in the rule under each record. This way the data is always attached to the right place and can never get mixed or lost.

Maybe the vba method to this could more easily be achieved if the data was also in the same row as its getting inserted under, I'm not sure.

Any VBA gurus out there that can help me out?