Mass insert in large database

[kisboros]

Dear Gurus,
I have a database of about 34,000 rows. These represent three months worth of driving data. I need to insert the home zip code "90058" (column I-ship_zip) BEFORE and AFTER each Route (column A). The routes have different number of stops, thus different number of rows. I have attached part of the file in question. How to do it?
Thank you very much.

[Richard Buttrey]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[jraj1106]

Hi
I have the soln. But can I upload it now? Asking becoz of that rule violation msg..

[kisboros]

I changed the title according to the rules. Please reply with your solution. Thank you!

[jraj1106]

Hi
Try this:
test file for forum.xlsm

Regards...

[kisboros]

Hi,
Unfortunately this didn't work. It just replaced the route numbers with duplicated zip codes. I need to put in "90058" prior and after each zip code in column I. The goal is to eventually calculate mileage between home office, routing stops, and back to home office.
Thanks.

[kisboros]

Hi,
Unfortunately this didn't work. It just replaced the route numbers with duplicated zip codes. I need to put in "90058" prior and after each zip code in column I. The goal is to eventually calculate mileage between home office, routing stops, and back to home office.
Thanks.

I am re-attaching a modified test file to see the effect I am trying to achieve.

[Richard Buttrey]

Hi,

One way:

Please Login or Register  to view this content.

[kisboros]

This is what I did: I opened up VBA editor; copied and pasted your code. Then I had to enable macros, and then I clicked on 'Run" and got an error message:
Compile error: Invalid outside procedure.
Would it make any difference that it is tab 7 in a workbook? I can see that 'Sheet 7' is highlighted on the sidebar.
Thank you.

[Richard Buttrey]

Hi,

The macro should be run with the sheet containing the data is the active sheet. It could be changed to refer to a specific sheet.

Is your real data EXACTLY the same layout as your example on which the macro is based?

[kisboros]

Hi Richard,
Yes it is. I cut and pasted from the original. Would this forum allow me to send the entire sheet to you to see?
Thanks,
Eva

[Richard Buttrey]

Hell Eva,


You don't need to send the entire workbook if it's very large. Just delete most of the rows but leave a good representative sample and upload.

Regards