VBA error: Object variable or with variable not set

Question

I receiving a strange error when running this subroutine in VBA: ``` Sub NameColumns() ' name key columns for later reference in formulas Dim startdatecol As Integer ' name start date column startdatecol = ActiveSheet.Cells.Find(What:="Start Date", after:=[a1], LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Column End Sub ``` Run time error '91': Object variable or With variable not set Any ideas on how I can fix this subroutine error? And why it is occurring? Thanks, AME

Accepted Answer

The problem is that `Find` is not finding the cell.

You will find (pun intended) that the following is true:

```
MsgBox ActiveSheet.Cells.Find(What:="Start Date", after:=[a1], LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False) Is Nothing
```

The first thing you should do is fix your search so that it finds the cell you're looking for.

Edit:

Maybe a change that would better illustrate the problem is this:

```
Dim found as Range
Dim startDateCol as Integer

Set found = ActiveSheet.Cells.Find(What:="Start Date", after:=[a1], LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not found Is Nothing Then startDateCol = found.Column

MsgBox startDateCol 'This will be zero if the "Start Date" cell wasn't found.
```

Edit to respond to comment:

```
'This should find the exact text "Start Date" (case sensitive) in the header row.
'A cell containing, for example "The Start Date" will not be matched.
Set found = ActiveSheet.Range("1:1").Find("Start Date", LookIn:=xlValues, _
                                           LookAt:=xlWhole, MatchCase:=True)
```

VBA error: Object variable or with variable not set

Solution

Problem

Related problems