Loop through column titles (1st row) if name is in array

[FuNeS13]

I know I'm doing something wrong in this part of the code, but I don't know what...

Dim MyArray As Variant
Dim found As Boolean
Dim uB As Integer, lB As Integer
Dim LC As Long

MyArray = Array("Type", "Number", "Order", "Invoice Date", "Due/Paid Date", "Amount", "Shipment", "Customer", "Name", "Credit Limit", "Chk/Ref")
LC = Cells(1, Columns.Count).End(xlToLeft).Column '56 columns
     uB = UBound(MyArray)
    lB = LBound(MyArray)
c = 1

For i = lB To uB
        Do Until c = LC + 1
                If Range(Split(Cells(1, c).Address, "$")(1) & "1").Value = MyArray(i) Then
                    found = True
                Else
                    found = False
                End If
                
                If found = False Then
                    Range(Split(Cells(1, c).Address, "$")(1) & ":" & Split(Cells(1, c).Address, "$")(1)).Select
                    Selection.Delete Shift:=xlToLeft
                Else
                GoTo nexti
                End If
        c = c + 1
        Loop
nexti:
Next i

what I want to do is if the titles on the first row exist in my array list, then I want to keep that column, if it doesn't exist then I want the column to be removed...

 

[Micron]

And the problem is? Aside from undeclared variables (i and C, something I virtually never do) one possible anomaly I see is that your loop is limited to the Ubound of your array (10) but your column count is 56 according to your code comments.
Should
For i = lB To uB be
For i = lB To LC ?
and do away with the Do Until ?
EDIT = maybe not. Will keep looking but I don't know what the issue is.

 

[Micron]

OK, I think your issue is that incrementing i is in the wrong place, or you need to increment it in more than one place, or you need to reset C before the outer loop loops again. C reaches the max in the inner loop and i is still 0 so nothing beyond the first array element is ever used. Getting late here now, so I have to leave it for this evening.
Except resetting C will mess up Range(Split(Cells(1, C).Address, "$")(1) & "1")

 

[FuNeS13]

both i and c are declared outside the sub-procedure, I have a code template I usually start my work with and have several things predeclared...

the original column count is 56, I want to find out if any of those columns -1st rows- have one of the 10 values in my array... if it doesn't then I want that column to be deleted...

My apologies for not making my request clear.

 

[FuNeS13]

OK, I think your issue is that incrementing i is in the wrong place, or you need to increment it in more than one place, or you need to reset C before the outer loop loops again. C reaches the max in the inner loop and i is still 0 so nothing beyond the first array element is ever used. Getting late here now, so I have to leave it for this evening.

okay let me review your answer and see if I can fix it based on your comment.

 

[Micron]

Note - I edited my last post.
Later ...

 

[Micron]

I keep saying I gotta quit but I didn't.
Add this before the Select
Debug.Print Range(Split(Cells(1, C).Address, "$")(1) & "1"), step through your code and keep looking at your sheet after the select. I see the selection moving to the 2nd column but since the first column was deleted (in my case) it moves onto the second and leaves the (now) first column alone. Usually when you delete rows/columns you count backwards from right to left or down to up as the case may be. That's because the rows/columns are shifting left/up and you don't want that.
PS. I meant I knew what you wanted to have happen, I didn't know what was/is wrong. Hope you didn't think I was too curt.
This time I have to put it away for the night!

 

[Akuini]

what I want to do is if the titles on the first row exist in my array list, then I want to keep that column, if it doesn't exist then I want the column to be removed...

Try this code:

Sub FuNeS13()

Dim c As Range, f As Range
Dim myHeader As String
myHeader = "|Type|Number|Order|Invoice Date|Due/Paid Date|Amount|Shipment|Customer|Name|Credit Limit|Chk/Ref|"

For Each f In Range("A1", Cells(1, Columns.Count).End(xlToLeft))
    If InStr(1, myHeader, "|" & f.Value & "|", vbTextCompare) = 0 Then
        If Not c Is Nothing Then
            Set c = Union(c, f)
        Else
            Set c = f
        End If
    End If
Next

c.EntireColumn.Delete

End Sub

 

[bebo021999]

Another way is to iterate through each cell in row 1, use the MATCH function to check for existence in MyArray. If it doesn't exist, change the value to an "error".
Then, use SpecialCells to highlight the column, and afterwards perform a single delete operation (in the code, use .select for verification, and if it's fine, replace it with .delete).

Dim LC As Long, myArray, ce As Range
LC = Cells(1, Columns.Count).End(xlToLeft).Column
myArray = Array("Type", "Number", "Order", "Invoice Date", "Due/Paid Date", "Amount", "Shipment", "Customer", "Name", "Credit Limit", "Chk/Ref")
For Each ce In Range("A1", Cells(1, LC))
    If Not IsNumeric(Application.Match(ce, myArray, 0)) Then
        ce.Value = "#N/A"
    End If
Next
Range("A1", Cells(1, LC)).SpecialCells(xlCellTypeConstants, xlErrors).EntireColumn.Select
'Range("A1", Cells(1, LC)).SpecialCells(xlCellTypeConstants, xlErrors).EntireColumn.Delete

 

[FuNeS13]

Try this code:

Sub FuNeS13()

Dim c As Range, f As Range
Dim myHeader As String
myHeader = "|Type|Number|Order|Invoice Date|Due/Paid Date|Amount|Shipment|Customer|Name|Credit Limit|Chk/Ref|"

For Each f In Range("A1", Cells(1, Columns.Count).End(xlToLeft))
    If InStr(1, myHeader, "|" & f.Value & "|", vbTextCompare) = 0 Then
        If Not c Is Nothing Then
            Set c = Union(c, f)
        Else
            Set c = f
        End If
    End If
Next

c.EntireColumn.Delete

End Sub

thanks, this worked exactly as I was expecting it... would you mind explaining it a little bit to me?