将Excel公式转换为VBA函数

Question

我是一名新手程序员，最初只是试图改进电子表格，但已超出了在Excel中使用基本函数。我有一个表格，我正在让函数查找第一列中的建筑物编号，然后查看两个其他各自列中的开始和结束日期，以确定是否应在日历工作表上填充特定区块。问题出现在同一栋楼房号可能会多次出现并带有不同的日期，我需要找到与正确日期匹配的条目。

我能够创建一个工作但复杂的公式来查找第一个实例，并学习到如果在false语句中再添加该公式的嵌套if并进行轻微更改，则可以继续执行该操作。我可以继续这样做，但它变得非常大而笨重。我正在尝试找到一种方法，使公式具有一个变量，该变量将查看已经使用多少次，因此它将继续搜索表格以寻找符合参数的答案。

以下是当前的公式：

=IFERROR(IF(AND(DATE('IF SHEET (2)'!$F$7,MATCH('IF SHEET (2)'!$C$2,'IF SHEET (2)'!$C$2:'IF SHEET (2)'!$N$2,0),'IF SHEET (2)'!C$4)>=VLOOKUP("2D11"&1,A2:F6,4,0),DATE('IF SHEET (2)'!$F$7,MATCH('IF SHEET (2)'!$C$2,'IF SHEET (2)'!$C$2:'IF SHEET (2)'!$N$2,0),'IF SHEET (2)'!C$4)<=VLOOKUP("2D11"&1,A2:F6,4,0)),IF(VLOOKUP("2D11"&1,A2:F6,3,0)="2D11",VLOOKUP("2D11"&1,A2:F6,6,FALSE)),"NO ANSWER"),"ERROR")

在你看到2D11&1的地方是我需要变量1的位置，它应该是“函数中使用次数+1”，然后我可以循环检查直到跑完所有的2D11或找到与之匹配的。我以前没有发过帖子，这次是通过大量的试错来完成的，所以如果您需要更多信息，请回复并告诉我，我会尽力提供。

因此，与其让某人来理解我发布的荒谬公式，我想简化一下，只说我需要完成的任务，然后尝试将其转换为VBA函数。所以我正在考虑几个步骤：

1. 将输出单元格所在行的建筑名称与A列中第一次出现的建筑名称进行匹配。
2. 与输出单元格相关联的日期>=第一个输入的开始日期（用户输入在D列中）。
3. 与输出单元格相关联的日期<=第一个输入的结束日期（用户输入在E列中）。
4. 如果第1、2和3部分都为True，则输入建筑物的第一个实例的Unit名称（位于F列）。
5. 如果第1、2或3部分为False，则循环查看下一列中建筑名称的实例。

希望这比公式更清晰一些，因为我仍然对VBA的了解很少，所以能够得到帮助。

- Darin Robinson

---

这是一种公式，如果你从建造开始就与之生活过，那么你可能会更好地理解它（我有这方面的经验）。你是否要从建筑编号、开始日期和完成日期开始？“查明是否应填充特定区块”是什么意思？ - chuff

我刚刚发现其中一部分可以删除，因为它是多余的。这个公式不再需要IF(VLOOKUP("2D11"&1,A2:F6,3,0)="2D11"，因为那部分是用来检查名称是否正确，但现在不再需要了。虽然删除它后，你还必须删除FALSE后面的一个)。 - Darin Robinson

更好的解释是，A列有建筑物编号，后面跟着在C列中出现次数的计数。B列和C列都是下拉框，B列是区域，因此在这种情况下是2D街区。然后，根据该街区中建筑物的块，C列具有下拉菜单。D、E和F列是用户填写的框。D列有占用开始日期，E列有占用结束日期。F列是他们输入将占用建筑物的人员。希望这能有所帮助。 - Darin Robinson

最好提供您的样本表格和所需内容，而不是那个永远不知道如何工作的长公式。 - matzone

我很乐意这样做，但我不确定如何上传表格。如果有人能告诉我该怎么做，我会完成上传的。 - Darin Robinson

请在问题更新中添加您的评论，以便它们可以得到适当的格式化并因此被理解。 - Pieter Geerkens

Answer 1

这里有一个简单的解决方案...

Building_name = ???
Date = ???

Last_Row = Range("A65536").End(xlUp).Row

For i = 1 To Last_Row
   if cells(i,1).value = Building_Name Then
       if date >= cells(i,4).value Then
           if date <= cells(i,5).value Then
               first instance = cells(i,6).value
           end if 
       end if
    end if
next

你应该在最后添加一个测试，以避免表中没有第一个实例的情况。

- lcrmorin

Answer 2

如果我理解正确，您有一个由3列组成的表T1：T1.building、T1.start date、T1.end date。 然后您有3个参数：P1=building，P2=start date，P3=end date。 您需要在表T1中找到符合输入参数日期的第一条记录，即：

• P1=T1.building
• P2<=T1.start date
• P3>=T1.end date

如果是这样，您可以定义一个自定义函数如下：

Public Function MyLookup(Key As Variant, DateMin As Variant, DateMax As Variant, LookUpTable As Range, ResultColumn As Integer) As Range
  Dim iIndx As Integer
  Dim KeyValue As Variant
  Dim Found As Boolean

  On Error GoTo ErrHandler

  Found = False
  iIndx = 1
  Do While (Not Found) And (iIndx <= LookUpTable.Rows.Count)
      KeyValue = LookUpTable.Cells(iIndx, 1)
      If (KeyValue = Key) And _
          (DateMin <= LookUpTable.Cells(iIndx, 2)) And _
          (DateMax >= LookUpTable.Cells(iIndx, 3)) Then
              Set MyLookup = LookUpTable.Cells(iIndx, ResultColumn)
              Found = True
      End If
      iIndx = iIndx + 1
  Loop
  Exit Function
ErrHandler:
  MsgBox "Error in MyLookup: " & Err.Description
End Function

这可能不是世界上最高效的代码，但我认为它很有解释性。

您可以下载this工作示例。

- user2970030