3

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

Community
  • 1
  • 1
AME
  • 5,234
  • 23
  • 71
  • 81
  • I was struggling with this some days ago, and the answer for this was changing the format of the cells in Excel. Due to the Excel cells I was searching for were numbers and the Find() method search strings – Luigi Lopez Mar 01 '18 at 14:09

1 Answers1

5

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)
mkingston
  • 2,678
  • 16
  • 26
  • If any of this doesn't make sense, just ask! :) – mkingston Aug 11 '12 at 21:58
  • Thanks, you're right the active cell was below the header row, so the subroutine was not able to find the header titled 'Start Date'. This raises another question, how can a limit the search function in the subroutine so that it only searches the header row and only returns exact matches? – AME Aug 11 '12 at 21:59
  • 1
    I've edited my answer to show how to find the exact text "Start Date" in the first row. I'm not sure that'll be your problem, based on what you've told me, but give it a go. – mkingston Aug 11 '12 at 22:05