Вот хороший и простой 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)