excel - 在多个工作簿中查找一个字符串，然后查找它旁边的单元格的值

Question

我在网上找到了一个代码，可以搜索多个工作簿，找到某个字符串，然后打印它的地址、工作簿、工作表和值。但是，我需要代码来打印地址、工作簿、工作表和旁边单元格的值。

Sub SearchFolders()
Dim fso As Object
Dim fld As Object
Dim strSearch As String
Dim strPath As String
Dim strFile As String
Dim wOut As Worksheet
Dim wbk As Workbook
Dim wks As Worksheet
Dim lRow As Long
Dim rFound As Range
Dim strFirstAddress As String

On Error GoTo ErrHandler
Application.ScreenUpdating = False

'Change as desired
strPath = "C:\DondeSoftware\Vo Temp\Mock Portfolios\Feng Zheyuan\Portfolio Snapshots"
strSearch = "Cash:"

Set wOut = Worksheets.Add
lRow = 1
With wOut
    .Cells(lRow, 1) = "Workbook"
    .Cells(lRow, 2) = "Worksheet"
    .Cells(lRow, 3) = "Cell"
    .Cells(lRow, 4) = "Text in Cell"
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder(strPath)

    strFile = Dir(strPath & "\*.xls*")
    Do While strFile <> ""
        Set wbk = Workbooks.Open _
          (Filename:=strPath & "\" & strFile, _
          UpdateLinks:=0, _
          ReadOnly:=True, _
          AddToMRU:=False)

        For Each wks In wbk.Worksheets
            Set rFound = wks.UsedRange.Find(strSearch)
            If Not rFound Is Nothing Then
                strFirstAddress = rFound.Address
            End If
            Do
                If rFound Is Nothing Then
                    Exit Do
                Else
                    lRow = lRow + 1
                    .Cells(lRow, 1) = wbk.Name
                    .Cells(lRow, 2) = wks.Name
                    .Cells(lRow, 3) = rFound.Address
                    .Cells(lRow, 4) = rFound.Value
                End If
                Set rFound = wks.Cells.FindNext(After:=rFound)
            Loop While strFirstAddress <> rFound.Address
        Next

        wbk.Close (False)
        strFile = Dir
    Loop
    .Columns("A:D").EntireColumn.AutoFit
End With
MsgBox "Done"

ExitHandler:
    Set wOut = Nothing
    Set wks = Nothing
    Set wbk = Nothing
 Set fld = Nothing
 Set fso = Nothing
 Application.ScreenUpdating = True
 Exit Sub

 ErrHandler:
 MsgBox Err.Description, vbExclamation
 Resume ExitHandler
End Sub

我想我可以添加一个变量，比如做类似的事情

Dim cashcell as Range
cashcell = Range(rFound.Row, rFound.Column + 1)

但是我错了。任何人帮助更改此代码？

Answer 1

要查找该单元格的值，请使用 .Offset例如右边的一列，它将是 rFound.Offset(,1).Value如果你想要地址，那么 rFound.Offset(,1).Address
如果你想返回一个范围对象，那么试试这个

Dim cashcell as Range
Set cashcell = rFound.Offset(,1)

您可能还想查看 Range.Offset property (Excel)