Hard Code Formatting

**ExcelJunkie** · 11-07-2006, 04:57 PM

Hi All:

If possible (without using a Macro), how would I hard code some VLOOKUP fomatting so that if someone INSERTS A ROW in the middle of some data, the new row will contain all the formatting contained in the previous row (without having to cut and paste formulas)?

For example, if rows 1 - 500 have:

Column A - input data
Column B = VLOOKUP(etc.etc.etc)
Column C = VLOOKUP(etc.etc.etc)

If someone inserts a new row at row 235, I would like columns B and C to already have the appropriate VLOOKUP formatting in place in row 235 without any copying and pasting. Is this possible?

Thank you!

**davesexcel** · 11-07-2006, 05:43 PM

Here's one version of insert row copy formula

Please Login or Register  to view this content.

**ExcelJunkie** · 11-09-2006, 12:44 AM

Hi Dave!

U. ROCK! The code compiles fine, saves fine, and when re-opened asks to enable Macros. All good.

However, it still isn't doing what I want. I may have mistated my question ... my apologies. I used the word FORMAT, which is correct, I do want it to preserve formatting, but I also meant FORMULAS.

if I insert a row, whatever FORMULAS are in each column in the PREVIOUS row, should automatically occur in the new row WITHOUT cutting and pasting (i.e, if there's a VLOOKUP formula in cells A234, B234, and C234 - and I insert a new row, I want ALL the formulas from cells A234, B234, and C234 to AUTO insert into row 235 as soon as I insert it). Is this possible? I think we're close.

Thank you so much! I appreciate it!

**davesexcel** · 11-09-2006, 07:30 AM

Not sure what you mean, when the rows are inserted, the empty rows are filled with the formulas from above, if you are getting wrong formulas, you possibly need to use absolutes in the formulas

**ExcelJunkie** · 11-09-2006, 12:19 PM

Hi Dave!

It's not that the formulas are wrong, there are none when new rows inserted. The rows are empty. I tried the code again, and it's not repeating any formulas from previous row (or any formulas period) when a new row is inserted. Here's the actual file and code:

http://www.natt.net/temp/LINE_CHECK.zip

What am I missing?

Thank you so much for your help!

**davesexcel** · 11-10-2006, 12:25 AM

Hi ,
You have the code in the worksheet module, it needs to be in a regular module, you should cut and paste it into a module like this

hit ALt F11 to get into VBA,
at the top menu ,select insert, select module, paste the code there, it should work then....

**ExcelJunkie** · 11-10-2006, 02:20 PM

Hi Dave:

Sorry to bother, but still not working? I followed your exact directions:

[hit ALt F11 to get into VBA,
at the top menu ,select insert, select module, paste the code there, it should work then....]

I checked my steps multiple times, made sure it was compiled, saved, etc. Yet when I insert a row, all the cells are blank in the new row?

Would it be possible for you to insert your code into the spreadsheet I uploaded, and see if it works? Otherwise, I must be doing something wrong again - my apologies.

**ExcelJunkie** · 11-10-2006, 02:23 PM

Actually, column A only seems to maintain the validation list functions when the new row is inserted, but all other columns B, C, D and some to the far right (helper cells) aren't auto inserting formulas from previous row?

**davesexcel** · 11-10-2006, 08:56 PM

I don't know ExcelJunkie..

I followed the steps as I directed you and did not have any problems....

are you selecting a cell before you start the macro??
Like really what's the problem here??

And what's the validation got to do with it?
I ran the macro again and there is no problem with the drop down menu being copied to the next row,
I don't no what the question is??

**ExcelJunkie** · 11-11-2006, 03:58 AM

Hi Dave: Sorry. You seem frustrated. I want to thank you again for your help. If you are not having any problems on your end, then I must be doing something wrong? I will continue to try it.

For now ... here's the steps I've repeated multiple times:

1. Copied your code into a MODULE, as per your directions in previous post - thanks for clarifying that for me, and thanks again for the code.

2. Compiled, Saved, Closed.

3. Re-opened, it asked me to enabled Macros at the prompt (Excel set on medium security to allow for Macros to be enabled) and I click ENABLE. I have no problem running many other spreadsheets with Macros.

4. Once opened, I simply insert a new row, let's say at row A32 on the first tab "Line Check DOWN". The drop down arrow appears in column A and allows me to choose - hence the validation list reference. In other words, what I was trying to say was it seems that your code works only for COLUMN A?

HOWEVER, the formulas you see in cells B31, C31, and D31 (the same formulas throughout those entire columns) are not in row 32? Those columns are BLANK? All the cells in ROW 32 are blank?

Before I sent this post, I followed the above steps again and got the same result - no formulas in row 32 were copied when I inserted a new row.

If my above steps aren't helpful for you to debug, and you are tired of trying, I thank you again anyway. If anyone else has any ideas on how to resolve this, please feel free to speak up.

Thank you all!

**davesexcel** · 11-11-2006, 05:00 AM

When you run the macro, the rows are inserted wherever your active cell is, you don't need to select a row or anything like that. Select cell G5 and run the macro, then select C8 and run it again, I believe this is what the problem is, place a button at the top of the sheet, whenever a user needs to insert a row, they can press the button and the input box pops up to ask how many rows you want to insert,

Here are other versions
of inserting rows and copying formulas,

http://www.mvps.org/dmcritchie/excel/insrtrow.htm

you should also bookmark this site, 70% of the time your answer will be there

http://www.mvps.org/dmcritchie/excel...htm#sitesearch