时间戳

首页 > 解决方案 > 如果在宏开始处未显示工作表，则 .find() 什么都不是

excel - 如果在宏开始处未显示工作表，则 .find() 什么都不是

问题描述

我的问题对我来说很奇怪。我正在搜索和阅读其他 VBA 问题，在我看来，这些问题与我的问题更加平等。我不知道。

用例： 宏应激活/选择活动工作簿中的工作表。然后在该活动工作表中找到日期/字符串。如果找到它会将范围保存到变量中。如果变量什么都没有，那么宏应该停止。

问题：如果宏启动时未选择/激活工作表，则 .find() 不起作用。每次我得到一个“无”的回报。如果通过宏启动（显示在我的显示器上）选择/激活了工作表，那么它可以工作。

我也在另一台电脑上检查了这个问题。但它也发生在那里......

    Dim rngFirst As Range
    Dim rngNext As Range
    Dim rngArea As Range
    Set rngArea = Range("A:Z")
    Dim defaultValue As Date, insertedDate As Date
    defaultValue = Format(Date, "dd.mm.yyyy")
    insertedDate = Application.InputBox(message, title, Format(defaultValue, "dd.mm.yyyy"), Type:=1)

    ActiveWorkbook.Sheets("mySheet").Activate
    Do
      If rngFirst Is Nothing Then
         Set rngFirst = rngArea.Find(What:=insertedDate, After:=rngArea(1))     'the problem start with this code line. What I got back is "Nothing"
         Set rngNext = rngFirst
         If rngNext Is Nothing Then
            MsgBox "There was a problem with finding the meeting date. Please check!" + Chr(10) + "The Macro will end here."
            Exit Sub
         End If
      Else
         'Do something with what was found
         'find the next entry in the sheet
         Set rngNext = rngArea.Find(What:=insertedDate, After:=rngNext)
         If rngNext.Address = rngFirst.Address Then Exit Do
      End If
    Loop

注意：不幸的是，我的工作 PC 上没有管理员……但我认为这并没有什么不同。

标签： excelvba

解决方案

解决了。这是一种错误的思维方式......我的问题下的评论解释了解决方案。

这是工作代码：

    Dim rngFirst As Range
    Dim rngNext As Range
    Dim rngArea As Range
    Set rngArea = Sheets("mySheet").Range("A:Z")       'added solution here
    Dim defaultValue As Date, insertedDate As Date
    defaultValue = Format(Date, "dd.mm.yyyy")
    insertedDate = Application.InputBox(message, title, Format(defaultValue, "dd.mm.yyyy"), Type:=1)

    Do
      If rngFirst Is Nothing Then
         Set rngFirst = rngArea.Find(What:=insertedDate, After:=rngArea(1))     'the problem start with this code line. What I got back is "Nothing"
         Set rngNext = rngFirst
         If rngNext Is Nothing Then
            MsgBox "There was a problem with finding the meeting date. Please check!" + Chr(10) + "The Macro will end here."
            Exit Sub
         End If
      Else
         'Do something with what was found
         'find the next entry in the sheet
         Set rngNext = rngArea.Find(What:=insertedDate, After:=rngNext)
         If rngNext.Address = rngFirst.Address Then Exit Do
      End If
    Loop

推荐阅读