VBA错误：对象变量或变量未设置 [英] VBA error: Object variable or with variable not set

问题描述

  Sub NameColumns（）
'name key列后面参考公式
 Dim startdatecol As Integer 
 
'名称开始日期列
 startdatecol = ActiveSheet.Cells.Find（什么：=开始日期，之后：= [a1]，LookIn：= xlValues，_ 
 LookAt：= xlPart，SearchOrder：= xlByColumns，SearchDirection：= xlNext，_ 
 MatchCase：= False，SearchFormat：= False）.Column 
 End Sub 
  

运行时错误'91'：对象变量或带变量没有设置

有关如何修复这个子例程错误的任何想法？为什么会发生？

谢谢，
AME

解决方案

问题是 Find 找不到单元格。

你会发现（双关语）以下是真实的：

  MsgBox ActiveSheet.Cells.Find（什么：=开始日期，之后：= [a1] ，LookIn：= xlValues，_ 
 LookAt：= xlPart，SearchOrder = = xlByColumns，SearchDirection：= xlNext，_ 
 MatchCase：= False，SearchFormat：= False）没有
  

您应该做的第一件事是修复搜索，以便找到您要查找的单元格。

编辑：

也许更好地说明问题是这样的：

  Dim找到范围
 Dim startDateCol as Integer 
 
 Set found = ActiveSheet.Cells.Find（what： =开始日期，之后：= [a1]，LookIn：= xlValues，_ 
 LookAt：= xlPart，SearchOrder：= xlByColumns，SearchDirection：= xlNext，_ 
 MatchCase：= False，SearchFormat：= False）
 
如果未找到则为Nothing然后startDateCol = found.Column 
 
 MsgBox startDateCol'如果开始日期细胞没有发现。 
  

编辑回覆评论：

 '这应该在标题行中找到确切的文本开始日期（区分大小写）。 
'包含开始日期的单元格将不匹配。 
 Set found = ActiveSheet.Range（1：1）。Find（开始日期，LookIn：= xlValues，_ 
 LookAt：= xlWhole，MatchCase：= True）
  

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

解决方案

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错误：对象变量或变量未设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！