Error on multiple find/select do loop

[Hosacans]

Hi All,

I have a spreadsheet with multiple columns that contains the same header. I want to write a macro that will delete the entire column with that header. here's what i have so far

    N(1) = "Pen %"
    N(2) = "LY R Sls "
    N(3) = "LY U Sls "
    N(4) = "Chg %"
    N(5) = "TY U EOH (DC) "
    N(6) = "TY R EOH (DC) "
    N(7) = "LY U EOH"
    N(8) = "LY U EOH (DC) "
    N(9) = "LY R EOH"
    N(10) = "LY R EOH (DC) "
    N(11) = "U Rcpts (DC)"
    N(12) = "R Rcpts (DC)"
    N(13) = "TY MD"
    N(14) = "TY MD %"
    N(15) = "LY MD"
    N(16) = "LY MD %"
    N(17) = "GM %"
    N(18) = "GM"
    N(19) = "S/T (STRS)"
    N(20) = "S/T (All STRS + DC)"
    
For a = 1 To 20

    Do
    
    On Error GoTo nextA
    
    Cells.Find(What:=N(a), After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Activate
   
    
    ActiveCell.EntireColumn.Select
    Selection.Delete Shift:=xlToLeft
    
   
    
    Loop

nextA:

Next a

but after going through the first instance "Pen %" i got a error on "LY R Sls"

Runtime error '91'
Object Variable or With block variable not set.

Please advise, i've been going at it with error handling for hours. it seems like the bug comes from within cell.find command. and somehow "LY R Sls" is out of range, but if that's the case, then how can "Pen % get through the loop fine and deleted all the columns with that header?

Thanks so much
Hosackies

 

[SkipVought]

Hi,

try this...

dim found as range

For a = 1 To 20

    Do
    
    On Error GoTo nextA
    
    Set found = Cells.Find(What:=N(a))
   
   If Not found Is Nothing Then
    
        ActiveCell.EntireColumn.Select
        Selection.Delete Shift:=xlToLeft
    
   End If
    
    Loop

nextA:

Next a

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[Hosacans]

Skip,

This is putting it through an infinite loop and deletes all the data.

Please advise.

Thanks
Hosackies

 

[SkipVought]

Sorry, I was not paying the attention i should have...

Dim found As Range

For a = 1 To 20

    Do
    
    On Error GoTo nextA
    
    Set found = Cells.Find(What:=N(a))
   
   If Not found Is Nothing Then
    
        found.EntireColumn.Delete Shift:=xlToLeft
    Else
        Exit Do
   End If
    
    Loop

nextA:

Next a

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[Hosacans]

Thanks Skip,

That worked wonderfully.
Hosacans