☰

在整个工作簿中搜索单元格内容并从该工作表中返回特定单元格

tag-icon • tag-icon microsoft-excel worksheet-function
在整个工作簿中搜索单元格内容并从该工作表中返回特定单元格

A3我正在尝试创建一个公式来获取（文本是）中指定的文本字符串，T1234-1234并在指定的行上的另一个工作簿中搜索该字符串，但在所有工作表上直到找到匹配项，并返回正在搜索的工作表中的另一个单元格。

以下是我目前的工作内容。此公式只能4372666_A.TXT在单元格中搜索指定工作表 () ，并且仅当位于工作表单元格中时才返回位于单元格中的A6值。A7A3A64372666_A.TXT

每次大约有 100 张纸需要搜索。

=IF(ISNUMBER(SEARCH(A3,'[EDICONFTESTEXCEL.xlsm]4372666_A.TXT'!$A$6)),LEFT(RIGHT('[EDICONFTESTEXCEL.xlsm]4372666_A.TXT'!$A$7,9),7),A3)

答案1

我建议您 VBA 代码在选定文件夹的所有工作簿中搜索文本字符串，并返回完整信息，如工作簿名称、工作表名称、单元格地址和文本字符串。

在任何现有工作表中输入下面写的代码作为模块。

    Sub SearchWorkbooks()

    Dim xFso As Object
    Dim xFld As Object
    Dim xStrSearch As String
    Dim xStrPath As String
    Dim xStrFile As String
    Dim xOut As Worksheet
    Dim xWb As Workbook
    Dim xWk As Worksheet
    Dim xRow As Long
    Dim xFound As Range
    Dim xStrAddress As String
    Dim xFileDialog As FileDialog
    Dim xUpdate As Boolean
    Dim xCount As Long

    On Error GoTo ErrHandler

    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    xFileDialog.AllowMultiSelect = False
    xFileDialog.Title = "Select the forlder"
    If xFileDialog.Show = -1 Then
        xStrPath = xFileDialog.SelectedItems(1)
    End If

    If xStrPath = "" Then Exit Sub
    xStrSearch = "Ravi"
    xUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    Set xOut = Worksheets.Add
    xRow = 1
    With xOut
        .Cells(xRow, 1) = "Workbook"
        .Cells(xRow, 2) = "Worksheet"
        .Cells(xRow, 3) = "Cell"
        .Cells(xRow, 4) = "Text in Cell"

        Set xFso = CreateObject("Scripting.FileSystemObject")
        Set xFld = xFso.GetFolder(xStrPath)
        xStrFile = Dir(xStrPath & "\*.xlsm*")

        Do While xStrFile <> ""
            Set xWb = Workbooks.Open(Filename:=xStrPath & "\" & xStrFile, UpdateLinks:=0, ReadOnly:=True, AddToMRU:=False)
            For Each xWk In xWb.Worksheets
                Set xFound = xWk.UsedRange.Find(xStrSearch)
                If Not xFound Is Nothing Then
                    xStrAddress = xFound.Address
                End If
                Do
                    If xFound Is Nothing Then
                        Exit Do
                    Else
                        xCount = xCount + 1
                        xRow = xRow + 1
                        .Cells(xRow, 1) = xWb.Name
                        .Cells(xRow, 2) = xWk.Name
                        .Cells(xRow, 3) = xFound.Address
                        .Cells(xRow, 4) = xFound.value
                    End If
                    Set xFound = xWk.Cells.FindNext(After:=xFound)
                Loop While xStrAddress <> xFound.Address
            Next
            xWb.Close (False)
            xStrFile = Dir
        Loop
        .Columns("A:D").EntireColumn.AutoFit
    End With
    MsgBox xCount & "cells have been found", , "Search String Across WBKs"
ExitHandler:
    Set xOut = Nothing
    Set xWk = Nothing
    Set xWb = Nothing
    Set xFld = Nothing
    Set xFso = Nothing
    Application.ScreenUpdating = xUpdate
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

怎么运行的：

  • 运行此宏。
  • 它将打开文件管理器对话框。
  • 选择存储所有工作簿的文件夹。
  • 单击“确定”完成。

Excel 将显示消息框，提示已找到多少个文本字符串。最后将创建新的工作表以显示结果，如下所示。

在此处输入图片描述

注意：

  • xStrSearch = "Ravi" 以及 .Columns("A:D").EntireColumn.AutoFit 都是可编辑的。

  • 您可以用另一个字符串替换“Ravi”，并将 A:D 列替换为其他字符串。

答案2

这是一个简单而美观的 UDF，其工作方式与内置函数类似SEARCH()，不同之处在于它搜索所有工作表并返回不同单元格的值而不是索引：

'============================================================================================
' Module     : <any standard module>
' Version    : 0.1.0
' Part       : 1 of 1
' References : N/A
' Source     : https://superuser.com/a/1332265/763880
'============================================================================================
Option Explicit

Public Function SEARCHALLSHEETS _
                ( _
                           ByVal find_text As String, _
                           ByVal within_cell As Range, _
                           ByVal return_cell As Range _
                ) _
       As Variant

  Dim strWithinCell As String: strWithinCell = within_cell.Address
  Dim strReturnCell As String: strReturnCell = return_cell.Address

  Dim wkstWorksheet As Worksheet
  For Each wkstWorksheet In within_cell.Parent.Parent.Worksheets
    If InStr(wkstWorksheet.Range(strWithinCell), find_text) > 0 Then
      Dim varReturnValue As Variant
      varReturnValue = wkstWorksheet.Range(strReturnCell).Value2
      Exit For
    End If
  Next wkstWorksheet
  SEARCHALLSHEETS = IIf(IsEmpty(varReturnValue), CVErr(xlErrValue), varReturnValue)

End Function


用法： SEARCHALLSHEETS(find_text, within_cell, return_cell)

安装后，您可以通过提供搜索目标单元格和返回单元格来使用它任何工作簿的一个工作表，它将搜索该工作簿的所有工作表。

如果它在任何工作表中都找不到搜索文本，它会#VALUE!像SEARCH()函数一样返回错误。


您需要重写公式来检测错误，而不是使用数字来确定是否找到匹配项。此外，还需要对其进行修改以使用新的返回值。

这是更新后的公式：

=IFERROR(LEFT(RIGHT(SEARCHALLSHEETS(A3,[EDICONFTESTEXCEL.xlsm]Sheet1!$A$6,[EDICONFTESTEXCEL.xlsm]Sheet1!$A$7),9),7),A3)

您的公式还有另一种更直接的转换方法，但要长得多。我将其包含在此处供您参考：

=IF(NOT(ISERROR(SEARCHALLSHEETS(A3,'[EDICONFTESTEXCEL.xlsm]4372666_A.TXT'!$A$6,'[EDICONFTESTEXCEL.xlsm]4372666_A.TXT'!$A$7))),LEFT(RIGHT(SEARCHALLSHEETS(A3,'[EDICONFTESTEXCEL.xlsm]4372666_A.TXT'!$A$6,'[EDICONFTESTEXCEL.xlsm]4372666_A.TXT'!$A$7),9),7),A3)

相关内容