Macro based on header search

I need a macro to run that will save Zip and Zip+4 in a certain format (00000 and 0000 respectively). The issue is that I have about 8 different files this macro needs to run on and the Zip and Zip+4 field is not in the same location every time. I know how to do the macro to run the formatting, I now need a macro that will search every cell in the first row and when it finds ZIP will format as needed (00000) and when it finds ZIP+4 will format as needed (0000). In addition to this, the field does not have common naming, sometimes it will simply be ZIP or Zip+4 but other times it will be CompanyZip and CompanyZip+4 so the search needs to search the cell contents for a non exact match. It also only needs to search the first row, nothing else.

It would be good (not needed) if we could also not apply the macro if the column is not populated past the first row. Some projects have multiple fields that will hold zip but not all projects will have that info populated.

This code will be part of existing code that is used to save each file as pipe (|) delimited so it does not need to be stand alone code.

Any help is greatly appreciated!! Feel free to ask any questions needed.

Hello cardinalsfan0510,

Welcome to the Forum!

This macro will open all ".xls" files in the specified folder and check each worksheet's first row for Zip and Zip+4 in the cells values. If there are not at least two rows on the worksheet, the macro will skip the worksheet. You can add this macro to your existing code by copying the code and pasting it into a separate VBA module. You will then need to call this macro from the existing code when needed.

You will need to change the file path to where your files are located. Currently, the macro searchs for ".xls" workbook files. You can change the extension if needed. Change the variable Ext in the code to the extension you need. Remember to include the period before the extension.

Please Login or Register  to view this content.

Hello cardinalsfan0510,

Are these converted files still Excel workbooks or text files?

Originally Posted by Leith Ross

Hello cardinalsfan0510,

Are these converted files still Excel workbooks or text files?

The files we open to modify are text files, pipe delimited. We then QA the data there and make any changes needed and run the macro shown previously to save it as a pipe delimited file again.

But when we edit the data and run the macro, it is open as a workbook.

EDIT: Here is what I have now. I think it is close. I got rid of a few things and simplified others. I made it only look at the open sheet (will only have one sheet open at a time) and changed the range to check only the first row as far as I need it to go. It will run without errors but will not save the file and the formats for the columns in question do not change.

Again, thanks for the help. I am sure i am just missing something small. I can upload a copy of the file I am testing with if that will help.

Please Login or Register  to view this content.

Hello cardinalsfan0510,

Well done! It is much more satisfying when you solve a problem yourself and you learn more in the process. Glad I could help.