Loop thru columns to check for match

[jeffreybrown]

I'm a little stuck on how to loop thru this array to show the correct sheet order. vCOLs represents what should be the headers for each column.

So in this macro, I would like the msgbox output to show what is currently in the column as the header and then show right next to each column header text what it header should actually be.

As you can see in the image, the current columns are 11, but the real columns should only be 9.

Sub CheckColumns()
Dim c As Long
Dim x As Long
Dim vCOLs As Variant
Dim ColumnLetter As String
Dim strValues As String
x = ActiveSheet.UsedRange.Columns.Count
Dim myCell As Range

    vCOLs = Array("Site", "Name", "Description", "Owner", "Model", "Device Pool", "Calling Search Space Name", "Calling Search Space Name", "Lines")
    With ActiveSheet

For Each myCell In Range(Cells(1, 1), Cells(1, x))
' For c = LBound(vCOLs) To UBound(vCOLs)
ColumnLetter = Split(myCell.Address, "$")(1)
strValues = strValues & "(" & ColumnLetter & ") " & myCell.Value & vbCrLf
' Next c
Next myCell

        MsgBox "Wrong Order with... " & vbCrLf & vbCrLf & strValues, vbOKOnly
    End With

End Sub

 

[Fluff]

How about

Sub CheckColumns()
Dim c As Long
Dim x As Long
Dim vCOLs As Variant
Dim ColumnLetter As String
Dim strValues As String
x = ActiveSheet.UsedRange.Columns.Count
Dim myCell As Range

    vCOLs = Array("Site", "Name", "Description", "Owner", "Model", "Device Pool", "Calling Search Space Name", "Calling Search Space Name", "Lines")
    With ActiveSheet

For Each myCell In Range(Cells(1, 1), Cells(1, x))
' For c = LBound(vCOLs) To UBound(vCOLs)
ColumnLetter = Split(myCell.Address, "$")(1)
If c <= UBound(vCOLs) Then
   strValues = strValues & "(" & ColumnLetter & ") " & myCell.Value & vbTab & vCOLs(c) & vbCrLf
Else
   strValues = strValues & "(" & ColumnLetter & ") " & myCell.Value & vbTab & "nothing" & vbCrLf
End If
c = c + 1
' Next c
Next myCell

        MsgBox "Wrong Order with... " & vbCrLf & vbCrLf & strValues, vbOKOnly
    End With

End Sub

 

[jeffreybrown]

That works wonderfully. Thanks Fluff.

 

[Fluff]

You're welcome & thanks for the feedback