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

Question

如果在 Sheet1!A:A 的字符串中找到 Sheet2!A2 值，则 Sheet1!"B2"= Offset(Sheet2!"A2",0,1)即，我在 Sheet2 上有一系列值，这些值是 Sheet1 上某个范围内的值的一部分。如果 Sheet2 上的值在 Sheet1 上的值范围内找到，那么我希望我的结果是 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

Answer 1

此公式将在工作表 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 并将该索引返回到外部索引公式。

然后返回该数组中 MATCH 索引处的值。