Checking Data column order

[guibgirl86]

I am working with a lot of data. I get the data from a source and then perform analysis with macros. I need to add a macro that will check to see if the data that I get is in the correct columns.

I was thinking of checking the headings in each column, and ideally moving the column to the correct place if it is not already there. I am having trouble getting this done. If i can't move the columns I would like a message to prompt the user to move the column to the correct spot.

Some of the columns I am looking for are

Name
Date/time
Status
Load1
Load2
Temp.

 

[AlphaFrog]

This thread may help.

http://www.mrexcel.com/forum/excel-questions/606890-reorder-columns-using-macro.html#post3007563

 

[guibgirl86]

I think I figured it out using the .Find function and a few If loops. It might not be elegant but it puts the columns I want into the order I want them in. And I can leave some of them blank if the columns are not there.

Dim acell As Range, bcell As Range, ccell As Range, dcell As Range, ecell As Range, fcell As Range
Dim gcell As Range, hcell As Range, icell As Range, jcell As Range, kcell As Range, lcell As Range
Dim col As Long


Sheets(1).Select
With Range("A1:J1")
Set acell = .Range("A1:k1").Find("Vessel", , xlValues, xlPart, xlByColumns, xlNext, False, False)
Set bcell = .Range("A1:k1").Find("Status", , xlValues, xlPart, xlByColumns, xlNext, False, False)
Set ccell = .Range("A1:k1").Find("Date/Time", , xlValues, xlPart, xlByColumns, xlNext, False, False)


    If Not acell Is Nothing Then
        col = acell.Column
        If col = 1 Then GoTo line1
        Cells(1, col).EntireColumn.Cut
        Cells(1, 1).EntireColumn.Insert
    Else
        MsgBox "Vessel Column not found."
line1:
    End If
    If Not bcell Is Nothing Then
        col = bcell.Column
        If col = 2 Then GoTo line2
        Cells(1, col).EntireColumn.Cut
        Cells(1, 2).EntireColumn.Insert
    Else
        MsgBox "Status Column not found."
line2:
    End If


    If Not ccell Is Nothing Then
        col = ccell.Column
        If col = 3 Then GoTo line3
        Cells(1, col).EntireColumn.Cut
        Cells(1, 3).EntireColumn.Insert
    Else
        MsgBox "Date/Time Column not found."
line3:
    End If
End With