Search multiple strings and return corresponding values on the same row from a X number of workbooks

[runforest]

Gents, I would be grateful for your help in tweaking below VBA code I found on www.get-digital-help.com.

Sub SearchWKBooks()
Dim WS As Worksheet
Dim myfolder As String
Dim Str As String
Dim a As Single
Dim sht As Worksheet
Dim RNG As Range
 
On Error Resume Next
Set RNG = Application.InputBox(prompt:="Select a cell range containing search strings" _
, Title:="Select a range", Default:=ActiveCell.Address, Type:=8)
On Error GoTo 0
 
Str = Application.InputBox(prompt:="Cell Offset:", Title:="Offset", Type:=2)
 
With Application.FileDialog(msoFileDialogFolderPicker)
    .Show
    myfolder = .SelectedItems(1) & "\"
End With
 
 
Set WS = Sheets.Add
 
 
WS.Range("A2") = "Path:"
WS.Range("B2") = myfolder
WS.Range("A3") = "Workbook"
WS.Range("B3") = "Worksheet"
WS.Range("C3") = "Cell Address"
WS.Range("D3") = "Link"
WS.Range("E3") = "Search string"
WS.Range("F3") = "Returned value"
 
a = 0
 
Value = Dir(myfolder)
Do Until Value = ""
    If Value = "." Or Value = ".." Then
    Else
        If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then
            On Error Resume Next
            Workbooks.Open Filename:=myfolder & Value, Password:="xxxx", WriteResPassword:="zzzzz"
            If Err.Number > 0 Then
                WS.Range("A4").Offset(a, 0).Value = Value
                WS.Range("B4").Offset(a, 0).Value = "Password protected"
                a = a + 1
            Else
                On Error GoTo 0
                For Each sht In ActiveWorkbook.Worksheets
                    For Each d In RNG
                        Set c = sht.Cells.Find(d, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
                        If Not c Is Nothing Then
                            firstAddress = c.Address
                            Do
                                WS.Range("A4").Offset(a, 0).Value = Value
                                WS.Range("B4").Offset(a, 0).Value = sht.Name
                                WS.Range("C4").Offset(a, 0).Value = c.Address
                                WS.Hyperlinks.Add Anchor:=WS.Range("D4").Offset(a, 0), Address:=myfolder & Value, SubAddress:= _
                                sht.Name & "!" & c.Address, TextToDisplay:="Link"
                                WS.Range("E4").Offset(a, 0).Value = d
                                WS.Range("F4").Offset(a, 0).Value = c.Offset(0, Str).Value
                                a = a + 1
                                Set c = sht.Cells.FindNext(c)
                            Loop While Not c Is Nothing And c.Address <> firstAddress
                        End If
                    Next d
                Next sht
            End If
            Workbooks(Value).Close False
            On Error GoTo 0
        End If
    End If
    Value = Dir
Loop
Cells.EntireColumn.AutoFit
End Sub

I use it all the time but is not fully fit for my needs as it still requires going through each returned values and before that, copying all required workbooks in one folder. Moreover, more than .one workbook is password protected (all with different passwords).
I managed to write array formulas to search for a string in various workbooks, but it takes ages to calculate results and requires typing passwords each time I open the file… It is not feasible, so I went back to the VBA code.
I look for strings across different workbooks in multiple locations to sought values that are in different columns on the same row in each workbook (i.e. I am looking for QWERTY in workbook 1 to return value from column A on the same row, in workbook 2 value from column G, in workbook 3 value from column D etc.).
It happens that searched strings might be in a different column within the same table but the returned value I am searching for would be always in the same column.
I look for strings because some cells have multiple lines of data and VLOOKUP or INDEX&MATCH won’t work.

Would you be able to adjust the code for a X number of static searches where paths to workbooks and offset variables are fed from range in a sheet (similar to X numbers of search strings), i.e. in cells:
A1: path to workbook 1;
B1 – C1 – D1: you specify columns to return corresponding values on the same row of a searched string
A2: path to workbook 1;
B2 – C2 – D2: columns to return corresponding values on the same row of a searched string
Etc.
Code looped until there is no path to workbooks.

What I am looking for:
1) Instead of going through all workbooks in a folder, I would like to search through a list of specific workbooks to which I provide paths
2) Offset variables (or columns) for returning values on the same rows to be manually input for each workbook (empty cell if only 1 value required)
3) Passwords for workbooks (ready only and modification passwords) to be sought from cell (on the same row where workbook path would be)

I hope this was clear enough...
Thanks and regards,
RunForest