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

Public Function GetFieldPosition(Name As String, FieldNames As Range) As Long
    On Error Resume Next
    GetFieldPosition = Application.WorksheetFunction.Match(Name, FieldNames, 0)
    Exit Function
End Function
Sub x()

    Dim lngFieldCol As Long
    
    lngFieldCol = GetFieldPosition("Customer PO", Range("A1:O1"))
    
    If lngFieldCol = 0 Then
        ' Does not exist
    Else
        ' check position
        
    End If
    
End Sub

**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.

Sub x()

    Dim lngFieldCol As Long
    Dim rngExpectedHeaders As Range
    Dim rngActualHeaders As Range
    Dim lngIndex As Long
    
    Set rngExpectedHeaders = Range("A1:O1")
    
    ' you need to get the actual headers from somewhere
    Set rngActualHeaders = Range("A1:O1")
    
    For lngIndex = 1 To rngExpectedHeaders.Cells.Count
        lngFieldCol = GetFieldPosition(rngExpectedHeaders.Cells(lngIndex).Value, rngActualHeaders)
    
        If lngFieldCol = 0 Then
            ' Does not exist
        Else
            ' check position
            
        End If
    
    Next
    
End Sub

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