vba - VBA Excel中的索引(匹配())与单元格中的公式

Question

第一次来这里。一般来说，编码很新。这样做只是为了好玩/尝试学习。提前感谢帮助。

我试图让我的代码查看一个 CSV 单元格并将值提取到一个数组中。然后，使用该数组 Index(Match()) 将最后一列中的日期放入一个新数组中。然后我将查看最新日期并将其写入 CSV 单元格旁边的单元格中。



这是我到目前为止所拥有的:

Dim DepArray As Variant, FinArray As Variant, x As Integer, i As Integer, y As Variant, StartDate As Date
    DepArray = Split(Target, ",")
    x = UBound(DepArray)
    'INDEX(MATCH()) to write to new array of finish dates for prior tasks
    For i = 0 To x
        y = Application.WorksheetFunction.Index(Sheets("SH1").Range("A1:E4").Value, Application.Match(DepArray(i), Sheets("SH1").Range("A1:A4").Value, 0), 5)
        FinArray(i) = y
    Next i
    StartDate = Application.WorksheetFunction.Max(FinArray)
    Target.Offset(0, 1).Value = StartDate

我在 y=Index(Match()) 行上收到类型不匹配错误。我认为，我已经尝试了每种变量类型，将 y 或者声明为整数和日期。有什么想法吗？

仅供引用，我正在使用 .Value 标注，因为在 A 列和 E 列中，项目编号和日期都是由单元格中的函数引入的。

此外，如果您有清理代码的技巧或发现我还没有解决的其他错误，我很乐意学习更好的实践。

谢谢!

更新了代码以帮助检查。我想我得到了 Match()，因为现在错误显示在 MsgBox y(i) 线上。我也试过作为 MsgBox y

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B1:B4")) Is Nothing Then
    Dim DepArray As Variant, FinArray As Variant, x As Integer, i As Integer, y As Variant, StartDate As Date
    DepArray = Split(Target, ",")
    MsgBox DepArray(0)
    x = UBound(DepArray)
    MsgBox x
    MsgBox DepArray(x)
    'INDEX(MATCH()) to write to new array of finish dates for prior tasks
    For i = 0 To x
        MsgBox DepArray(i)
        y = Application.Match(DepArray(i), Sheets("Sheet1").Range("A1:A4").Value, 0)
        MsgBox y(i)
        'FinArray(i) = Application.WorksheetFunction.Index(Sheets("Sheet1").Range("A1:E4").Value, Application.Match(DepArray(i), Sheets("Sheet1").Range("A1:A4").Value, 0), 5)

    Next i
    StartDate = Application.WorksheetFunction.Max(FinArray)
    Target.Offset(0, 1).Value = StartDate
End If


End Sub

Answer 1

此行创建 DepArray作为字符串数组:

DepArray = Split(Target, ",")

因为 Split返回一个字符串数组。

稍后，当你这样做时:

y = Application.Match(DepArray(i), Sheets("Sheet1").Range("A1:A4").Value, 0)

Match函数返回 1004 错误，因为没有 "1" 的值在该范围内，取而代之的是 1 的值!

您可能可以通过转换为长/整数来解决:

Dim valueToMatch as Long
valueToMatch = CLng(DepArray(i))
y = Application.Match(valueToMatch, Sheets("Sheet1").Range("A1:A4").Value, 0)

捕获错误通常也是一个好主意，例如:

If IsError(y) Then
    MsgBox "Unable to find " & CStr(valueToMatch)
    ' Exit sub, or go to next iteration, etc.
End If

您还需要 ReDim FinArray ，在这一行之后:

x = UBound(DepArray)
ReDim FinArray(x)

并取消注释将值分配给此数组的行，否则它仍然是 Empty变体和 Max(Empty)将返回 0每次:D

另外，你需要做 Application.EnableEvents = False在您的程序开始时，然后是 Application.EnableEvents = True在程序结束时，为了避免可能的无限循环，因为 Target.Offset(0, 1).Value = StartDate将触发 Change事件 :)