A3
我正在尝试创建一个公式来获取(文本是)中指定的文本字符串,T1234-1234
并在指定的行上的另一个工作簿中搜索该字符串,但在所有工作表上直到找到匹配项,并返回正在搜索的工作表中的另一个单元格。
以下是我目前的工作内容。此公式只能4372666_A.TXT
在单元格中搜索指定工作表 () ,并且仅当位于工作表单元格中时才返回位于单元格中的A6
值。A7
A3
A6
4372666_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)