Checking Data column order

guibgirl86

New Member
Joined
May 14, 2013
Messages
22
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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

guibgirl86

New Member
Joined
May 14, 2013
Messages
22
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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,579
Messages
5,637,208
Members
416,960
Latest member
Carbon1198

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top