Check if Column Exist

**izet99** · 08-20-2009, 10:33 AM

Hi, I have question...I have been search around but I couldn't find anything that would fit my needs.

How I can check that certain column exist and each column is right sequence.

I have put together macro that runs on my reports, reports coming from multiple source, right now macro run fine but I would like to create some type of validation to check if columns exist or have extra column and they are in right place otherwise my macro might blowout. My micro is not dynamic to recognize specific column by name. So if i have this validation it would warning me about changes in report design so I can alter my macro or just move extra column to the lust column where I don't have any calculation before I run my macro.

Is this possible to make:

- Check for column heading if it exist in range from A1:O1 or Array of column names
if exist = do nothing
Else = let me know, some type output message to a user and stop macro
- Check if column is in right sequence
if in right sequence = do nothing
Else = let me know, some type output message to a user and stop macro
- Check what's is new, what column is added
if new column is added between any of column in range then = move to the first empty/available column
Else = do nothing

Rest of macro will executed from here

If somebody has any ideas on how to do this it would be of great help to me. I'm have attached sheet with column heading only.

Thanks a lot for any help on this issue.

**Andy Pope** · 08-20-2009, 10:45 AM

you could use the Match formula to report existing and location of fieldname

Please Login or Register  to view this content.

**izet99** · 08-20-2009, 11:25 AM

Hmm, I'm sorry but I don't get it....

Do I have to repet second peace of code for each column name to validate?

Thanks

**Andy Pope** · 08-20-2009, 11:35 AM

You need to test as many columns as you need to test.

If you have an array of column names the loop through that array.

You don't explain how you know what the order should be or what the fieldnames are.

**izet99** · 08-20-2009, 12:39 PM

Hi thanks for your prompt response,

I have uploaded excel file in my first post, field names or column names are included and names are in exact sequance as they should be.

So when macro run, if that first row is not in exact format, give me some type warning so I can edit file, or if it finds any new column that have been inserted between any of those column names it should move it to first empty column

Thanks

**Andy Pope** · 08-21-2009, 04:17 AM

Your file contains 1 set of information. I have assumed this is the requried headers in the correct order.

Please Login or Register  to view this content.

So somehow you need to obtain the actual headers, which I assume are coming from a file you open or some other location.

**izet99** · 08-21-2009, 11:35 AM

Hmm, that's good point, I guess there should be some type of array with list of header names, that list should be checked/matched against headers in the file that have been opened.

Let say, if header name in array in first postion match the header in first column of the open file then its great no action required, go next.

If header does not match, give me some message output and macro stops.

I'm sorry, I probably getting you confused with all this but I really appricate your help on this.

Thanks.

**Andy Pope** · 08-21-2009, 02:43 PM

That's basically what the code does.

You just need to feed it the 2 bits of information about Expected and actual headers.
The add code in the two parts of the code where I have left comments.