VBA Code to Copy Data and reformat it between two workbooks

[Granite-Granny]

I'm trying to write code in Airport1.xlsm to allow me to create a macro to automatically copy data from Airport-Data.xlsm and reformat it to what is needed in Airport1.xlsm.

Basically I need the code to take each airport in column A of Airport-Data and where there is a non-zero value in rows column C to G I need it to clear the appropriate column in Airport1.xlsm and add a 1 to the appropriate box.

I got stuck thinking about the nested For Next Cell in range procedure and how to execute it with a search for the correct row in Airport1.xlsm.

GG

[k64]

It's not clear what you're trying to do. In your sample, please do a couple manually, highlight them, and explain how you did it.

[Granite-Granny]

I uploaded new files and colored the cells to help describe what I'm looking for.

Starting at the Airport-Data workbook each airport in yellow has up to 5 brands associated with it. I need to transfer that data from the Airport-Data workbook to the Airport1 workbook (blue cells to blue cells). However I need the data instead of being AB, CD, WE, FR or CZ as per Airport Data it needs to be 1 or a blank. It's not just a case of copy and paste because the airports (yellow cells) change and may not be in the same order on the Airport-Data and Airport1 sheets. So the macro needs to search for the given airport in Airport1 workbook and then place the corresponding data in the brand cells with a 1 or blank.

GG

[Granite-Granny]

Well I think I managed to do what I needed. It just took me all day to figure out. At least I'm learning.

If anybody has any tips on how to improve this code in terms of efficiency or coding practice I'd like to hear.
Anyway this is the code I used:

Please Login or Register  to view this content.

GG

[k64]

Well that certainly helps me understand what you were trying to do . Here is more efficient/concise code

Please Login or Register  to view this content.

[Granite-Granny]

K64,

Thanks for the code but I don't think it will work based on one critical piece of information that I realize you'd have to be clairvoyant to know. The workbooks I used here are just workbooks I created to help me understand the coding without dealing with massive amounts of data. They are subsets of the real thing.

The real Airport1 worksheet is several hundred lines long. The Airport-Data sheet is a subset of the Aiport1 sheet and may only be 50 lines long with the rows not always in the same order. The arrays in your code will not be the same size so I think ultimately it has to be done with a search function.

GG

[k64]

My code doesn't require the arrays to be the same size.

[Granite-Granny]

Ah you are right. I made that assumption by looking at your code and not really understanding it. I have to look at it some more and figure out what it is doing.

The other thing your code doesn't do is return an error if one on the codes in the data file is not on the Airport file. That's a good and bad thing. Good because the code will run without interruption bad because I want the user to be aware of which airports aren't listed.

GG

[Granite-Granny]

Because I'm not fully understanding your code I'm continuing with mine for now. I have a problem with the error handling. I'm trying to put in an error handling statement kind of like an if then. What I want to do is IF error, display a message box, then go back up to the next rCell. I know this is not the right way to do this but I came up with the following code. It still shows an error because of the very last time the code hits "MissingAirport".

Please Login or Register  to view this content.

Ideally what I'd like to do is finish the data transfer and have any airports that are not in the list, put into an array and then shown all at once at the end in a msgbox.

GG

[k64]

Hi Granite-Granny,

You asked for help making your code more efficient and following better coding practices. I have provided code that does that. It is simple to add any airports that weren't on the list to a Range or Message Box. If you would like me to explain my code and to help you code better, I'm happy to do that. If you would rather do this yourself, I'm happy to let you do that. Please let me know which you prefer.

K64

[Granite-Granny]

K64,

Thanks for your help. I really do appreciate it. I'd like to understand your code because obviously looking at the length of it, it is much more efficient than mine. I'm just learning VBA so I don't want to use code until I really understand it because that won't help 6 months down the line when I have to modify it for something else.

I understand that you have assigned values to 2 arrays corresponding to the data in each spreadsheet.

It looks like the search function is accomplished by

Please Login or Register  to view this content.

But to me to looks like the arrays have to line up in order for a search to be a match. Obviosuly that's not the case because when I run the code on the sheet it works. But I don't understand ......Doh!

I think I just figured it out. You go down the one dimensional array 1 matching up for the first item in array 2. Once that is done then you move onto the second line of array 2 and then try to match every item in array 1 until it hits a match etc etc.

So how would you create a list of airports not found in the Airport1 list (arr1)?

GG

[k64]

Exactly! Here are two ways to do it (1 is commented out)

Please Login or Register  to view this content.

[Granite-Granny]

k64,

That works. Thanks for your help on this.

GG

[k64]

You're welcome. If you want me to explain any part of my code just ask. If your question is answered, mark this thread as SOLVED and if you feel I've been helpful, feel free to give me +Rep. Glad I could help

[Granite-Granny]

You bet. Your help was very much appreciated. Your approach was something I wouldn't have thought about because of my lack of experience with multi-dimensional arrays. You helped me learn stuff about arrays and pointed me to understand Ubound and LBound better. Thanks,

GG

[k64]

You're definitely welcome. That was my first experience posting here. I worked for a long time on code and someone tore it up and posted alien-looking code that used an array. It worked cleaner and faster, so I learned to use arrays and write all at once, to make my code more efficient.