excel - 如何在Excel VBA中偏移索引匹配结果

如果在Sheet1！A：A的字符串中找到Sheet2！A2值，则Sheet1！“ B2” =偏移量（Sheet2！“ A2”，0,1）
即，我在Sheet2上有一个值范围，这是Sheet1范围内的值的一部分。如果在Sheet1的值范围内找到Sheet2的值，那么我希望我的结果是Sheet1的相邻值是Sheet2的相邻值。
如何在VBA中做到最好？


这是将公式合并到以下代码中的代码：
该代码现在可以使用了

Sub ListAllFiles()

    Dim objShell As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim ws As Worksheet
       Set ws = Sheets("Sheet1")
    Dim myExt1 As String
    Dim myExt2 As String
        myExt1 = "pptx"
        myExt2 = "pdf"

    Set ws = Sheets("Sheet1")
    Set objShell = CreateObject("Shell.Application")
'Get the folder object associated with the directory
Set objFolder = objShell.Namespace("\\sitefs\www\htdocs\c13\comm\IMS\Schedules\")

    Dim objFolderItems As FolderItems
    Dim objFolderItem As FolderItem
        Set objFolderItems = objFolder.Items()

'Loop through the Files collection
Dim fOwner As String
Dim fAuthor As String
Dim dlm As String

ws.UsedRange.Clear
 ws.Cells(1, 1).Value = "The current files found in " & objFolder & " are:"
 ws.Cells(1, 2).Value = "Date Last Modified"
 ws.Cells(1, 3).Value = "Owner"
 ws.Cells(1, 5).Value = "Report Date:"
 ws.Cells(1, 6).Value = Now
 Range("A1").EntireRow.Font.Bold = True
  For Each objFile In objFolder.Items
  dlm = objFolder.GetDetailsOf(objFile, 3)
  If InStr(objFile.Name, "FINAL") Then GoTo 50
   If Year(CDate(dlm)) < Year(Now) Then GoTo 50
   If InStr(objFile.Name, myExt1) Or InStr(objFile.Name, myExt2) Then
    Dim x As Integer
    x = ws.UsedRange.Rows.Count + 1
        ws.Cells(x, 1).Value = objFile.Name
        'Link cell to file
            Cells(x, 1).Select
                Selection.Hyperlinks.Add Anchor:=Selection, Address:=objFile.Path
        ws.Cells(x + 0, 2).Value = dlm
    End If
50 Next

'Added this and it's now working
 Dim oRng As Range
   Dim cRng As Range
   x = ws.UsedRange.Rows.Count
  Set oRng = Range("C2")
  Set cRng = Range("C2:C" & x)
    oRng.FormulaArray = _
        "=OFFSET(INDEX(Sheet2!$A$2:$A$19,MATCH(TRUE,INDEX((ISNUMBER(SEARCH(Sheet2!$A$2:$A$19,a2))),),0)),,2)"
   cRng.FillDown

 Columns("A:B").Sort key1:=Range("B2"), _
      order1:=xlDescending, Header:=xlYes
    Columns.AutoFit
     'Clean up!
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing
    Set oRng = Nothing
Set cRng = Nothing
End Sub

最佳答案

此公式将在工作表1中的值中找到工作表2中的值，并返回适当的值：

=INDEX(Sheet2!$B$2:$B$9,MATCH(TRUE,INDEX((ISNUMBER(SEARCH(Sheet2!$A$2:$A$9,A2))),),0))

怎么了：

内部INDEX正在创建一个TRUE / FALSE数组，在这种情况下为8个对象。

因为当遍历Sheet2上的值时，它要么返回数字，要么返回错误。

ISNUMBER根据该结果返回TRUE / FALSE。

然后，MATCH遍历数组，直到找到TRUE，并将该索引返回到外部Index公式。

然后，它返回该数组中的值，该值位于MATCH的索引处。