时间戳

首页 > 解决方案 > 如何在 VBA 中使用索引/匹配设置数组公式以获取第一个匹配单元格

vba - 如何在 VBA 中使用索引/匹配设置数组公式以获取第一个匹配单元格

问题描述

我正在尝试在 for 循环中设置数组公式，以将字符串与另一个名为Project Name的工作表上的名称列表进行部分匹配。这应该是我使用exceljet的方法在电子表格中得到公式的最终产品，但是当我尝试将其转换为 VBA 时遇到了“需要对象”的错误。cells(i,6) 是我试图与项目名称进行部分匹配的字符串的位置。该列不必是“6”，它是请帮助的地方。谢谢！

Sub Shortname()

Dim SRng As Variant
Dim SName As Integer
Dim SNrow As Integer
Dim PLcol As Integer
Dim PLrow As Integer

     Worksheets(3).Activate

     SNrow = Cells(Rows.Count, 1).End(xlUp).Row

     SRng = Range(Cells(2, 1), Cells(SNrow, 1)).Value

     Worksheets(2).Activate

     PLcol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
     PLrow = Cells(Rows.Count, 1).End(xlUp).Row

     For i = 2 To PLrow

         Cells(i, PLcol).Value = Application.WorksheetFunction.Index(SRng, Application.WorksheetFunction.Match("TRUE", Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(SRng.Value, Cells(i, 6))), 0), 1)

     Next i

End Sub

标签： vbaindexingmatcharray-formulas

解决方案

神秘变量 (SRng)

我会写这样的代码（不是解决方案，只是一个更易读的版本）：

Sub Shortname()
    Dim SRng As Variant
    Dim SName As Integer
    Dim SNrow As Integer
    Dim PLcol As Integer
    Dim PLrow As Integer
    'Missing Declaration
    Dim i As Long
    'To avoid jumping around worksheets do NOT use Activate or Select
    With Worksheets(3)
        SNrow = .Cells(Rows.Count, 1).End(xlUp).Row
        SRng = .Range(Cells(2, 1), Cells(SNrow, 1)).Value
    End With
    With Worksheets(2)
        PLcol = .Cells(1, Columns.Count).End(xlToLeft).Column + 1
        PLrow = .Cells(Rows.Count, 1).End(xlUp).Row
    End With
    With Application.WorksheetFunction
        For i = 2 To PLrow
            'Run-time error 424: Object required
            Worksheets(2).Cells(i, PLcol).Value = .Index(SRng, .Match("TRUE", _
                .IsNumber(.Search(SRng.Value, Cells(i, 6))), 0), 1)
        Next i
    End With
End Sub

错误（神秘）在于 SRng 变量。为什么它被声明为变体？如果它是一个字符串，则将其声明为字符串并将该行更改SRng = .Range(Cells(2, 1), Cells(SNrow, 1)).Value为 

SRng = .Range(Cells(2, 1), Cells(SNrow, 1)).Address

如果它是 Range 对象，则将其声明为 Range 和 remove .Value，但是由于您在语句的Search部分中使用了For Next循环，您仍然会收到错误，但是范围没有值（也许您想使用某些东西像这样）。SRng.ValueSRng.Cells(i, 6).Value

如果这不能帮助您提供更多信息，例如工作表示例，以查看单元格、范围内的内容......并解释您在 For Next 循环中搜索的内容。

推荐阅读