Code to return column # of matching string returning runtime error 91

[mellymelle]

This is part of a larger code to find column numbers of matching headings and deleting such columns.

Dim DateFind As Range, DateCol As Long
Set DateFind = Sheet1.Rows(1).Find(What:="Date", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
DateCol = DateFind.Column
Columns(DateCol).EntireColumn.Delete

The code is returning runtime error 91 object variable or with block variable not set...any ideas?

 

[Rick Rothstein]

Remove the .Column that you have on the end of your Set code line... or, perhaps, change it to Columns (note the "s" on the end) depending on what range you are actually trying to set.

 

[V_Malkoti]

I'm guessing that the error is on this line:

Set DateFind = Sheet1.Rows(1).Find(What:="Date", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column

If yes, then its probably because Find method cannot find any cell with text "Date" in the row and returns a null object. So trying to read Column property of a null object causes this error.

 

[Fluff]

How about

Sheet1.Rows(1).Find(What:="Date", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).EntireColumn.Delete

You have declared DateFind as range, but were trying to give it a value

 

[JoeMo]

You've dimensioned DateFind as a range object, but you are "setting" it to a column number which is a long. You have two choices:

1. Dimension DateFind as a range and remove the .Column from the end of your Find, or
2. Dimension DateFind as Long and remove the Set keyword from the Find line.

 

[mellymelle]

Thank you all for your quick replies! Much appreciated all around.
JoeMo - thanks for the detailed explanation that helped me learn something for the day.
Fluff - your intuition is correct as all I'm looking to do is delete the columns with such headings much better to use the more straightforward code you provided.

 

[Fluff]

Glad We could help & thanks for the feedback