Macro/VBA: Keyword Search through multiple worksheets

[ineedhelpppl]

Hello, I am attempting to create a code that will search a keyword typed from one workbook/worksheet and search that said keyword through multiple worksheets in a different workbook. Additionally I want to bring back the worksheet name the keyword was found in (which would be named the Years 2005-2015) along with the sentence that the word was found in. I have a complicated code that I cannot seem to get working with problems like subscript out of range, or object not defined properly. I have this errors with attempting to select sheets("WKS").Select and sheets(ReportYear).select.
I feel like I am missing something simple but have no clue how to get this code to work. If anyone could please help me out I would be so grateful.

Sub MultiYearSearch()

Dim UPS_File As String

Dim UPS As Workbook
Dim FS As Workbook
Dim WKS As Worksheet
UPS_File = "C:\Users\lsbscmi\Desktop\User\Search\UPS.xlsm"

Set UPS = Application.Workbooks.Open(UPS_File)
Set FS = Workbooks("FS.xlsm")
Set WKS = FS.Worksheets("WKS")

Workbooks("UPS").Activate


Dim ReportYear As Worksheet
Dim i As Long
Dim Years()
i = 0

For Each ReportYear In ActiveWorkbook.Worksheets
i = i + 1
ReDim Preserve Years(1 To 12)

Years(1) = "2005"
Years(2) = "2006"
Years(3) = "2007"
Years(4) = "2008"
Years(6) = "2009"
Years(7) = "2010"
Years(8) = "2011"
Years(9) = "2012"
Years(10) = "2013"
Years(11) = "2014"
Years(12) = "2015"

Next ReportYear

For i = LBound(Years) To UBound(Years)
FindKeywordList (i)
Next i

End Sub

Sub FindKeywordList(Year As Integer)

Workbooks("FS").Activate
Sheets("WKS").Select

Dim row_counter, lastrow_results
'Dim ReportYear As String

Application.ScreenUpdating = False

'ReportYear = InputBox("What year does the annual report cover?", "Annual Report Year")



'Loop through the keywords in rows 2 through 53 in column BB
For row_counter = 2 To 51

If Len(Cells(row_counter, 54).Value) > 0 Then
'Put the next keyword in the list in cell A7 that the FindKeyword function references
Range("A7").Value = Cells(row_counter, 54).Value

'When the results are complete, the last row of results is returned from the FindKeyword function
Application.ScreenUpdating = False
lastrow_results = FindKeyword(Year)
Application.ScreenUpdating = True

'Select and copy all the results
Range(Cells(10, 12), Cells(lastrow_results, 12)).Select
Selection.Copy

'Paste the results below the last row of saved results in column BC
'Find the last row - if only the header row exists, BC10 will be blank
If Range("BC53").Value = "" Then
lastrow_savedresults = 52
'Otherwise find the last row of data
Else
Range("BC52").Select
Selection.End(xlDown).Select
lastrow_savedresults = Selection.Row
End If

Cells(lastrow_savedresults + 1, 55).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Paste the annual report year and keyword next to each row in the saved results
'Start in the row after the LastRow_SavedResults (found above)
'Since results start in column L on row 10, results with a lastrow_results=10 actually only has one row of results
'So (lastrow_results - 9) is the number of rows past LastRow_SavedResults the keyword should be updated
For i = (lastrow_savedresults + 1) To (lastrow_savedresults + lastrow_results - 9)
Cells(i, 53).Value = Year
Cells(i, 54).Value = Range("A7").Value
Next i
End If


Next row_counter


Application.ScreenUpdating = True
Range("BC1").Select

'Sheets("PastedAnnualReport").Select
'Range("A1").Select


End Sub




Function FindKeyword(Year As Integer)
'
' FindKeyword Macro
'


'
Dim ReportYear As String 'converts year sent here to a string for use in the name of the worksheet

Dim Keyword, isKeywordFound
Dim FoundRow, FoundColumn
Dim PositionCounter, CharacterPosition, CharacterLength, period_rowcounter, results_rowcounter
Dim period_rowcounter_start, period_rowcounter_end

ReportYear = CStr(Year)

period_rowcounter = 9 'counter is incremented before its updated
results_rowcounter = 9 'counter is incremented before its updated

'Get the Keyword being searched for
Sheets("WKS").Select
Keyword = Range("A7").Value

Sheets(ReportYear).Select
Range("A1").Select
isKeywordFound = True 'enter the loop for the first time
'FoundRow = 1
'Execute as long as another instance of the keyword is found
While isKeywordFound = True
'Go to the ReportYear sheet and find the keyword
Sheets(ReportYear).Select
Set Rangeobject = Cells.Find(What:=Keyword, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlDown, MatchCase:= _
False)


If Rangeobject Is Nothing Then
'MsgBox ("'" & Keyword & "' was not found")
isKeywordFound = False
Else
Rangeobject.Select
End If


'Execute if another instance of the keyword is found AND one of these is true:
' The active row is greater than the previous row
' The active row is the same as the previous row, but the active column is greeater than the previous column
If isKeywordFound = True And (ActiveCell.Row > FoundRow Or (ActiveCell.Row = FoundRow And ActiveCell.Column > FoundColumn)) Then
'Record where it's located on the sheet
FoundRow = ActiveCell.Row
FoundColumn = ActiveCell.Column

'MsgBox ("Current Cell is Row " & FoundRow & ", Column " & FoundColumn)

'----------------------------------------------------
'MAP THE LOCATIONS OF THE PERIODS IN THIS TEXT STRING
'----------------------------------------------------
PositionCounter = 1
CharacterPosition = 1


'first row should always show character position 1
Sheets("WKS").Select
period_rowcounter = period_rowcounter + 1
Cells(period_rowcounter, 1).Value = FoundRow
Cells(period_rowcounter, 2).Value = FoundColumn
Cells(period_rowcounter, 3).Value = 1
period_rowcounter_start = period_rowcounter 'first row of data for periods

While CharacterPosition > 0

'Convert both strings to Uppercase then match the search string to this cell's string
'CharacterPosition denotes where in the string the keyword begins
'Syntax is: InStr( [start position], string, substring, [1 for text comparison] )
Sheets(ReportYear).Select
CharacterPosition = InStr(PositionCounter, UCase(Cells(FoundRow, FoundColumn)), UCase("."), 1)
CharacterLength = Len(Cells(FoundRow, FoundColumn))

If CharacterPosition > 0 Then
'Record location of keyword on the Keyword Search sheet
Sheets("WKS").Select
period_rowcounter = period_rowcounter + 1
Cells(period_rowcounter, 1).Value = FoundRow
Cells(period_rowcounter, 2).Value = FoundColumn
Cells(period_rowcounter, 3).Value = CharacterPosition
End If

PositionCounter = CharacterPosition + 1
Wend

'last row should always show the last character position in the cell
Sheets("WKS").Select
period_rowcounter = period_rowcounter + 1
Cells(period_rowcounter, 1).Value = FoundRow
Cells(period_rowcounter, 2).Value = FoundColumn
Cells(period_rowcounter, 3).Value = CharacterLength
period_rowcounter_end = period_rowcounter 'Record the last row of data for the periods in this cell

'-----------------------------------------------------
'MAP THE LOCATIONS OF THE KEYWORDS IN THIS TEXT STRING
'-----------------------------------------------------
PositionCounter = 1
CharacterPosition = 1
While CharacterPosition > 0
'Convert both strings to Uppercase then match the search string to this cell's string
'CharacterPosition denotes where in the string the keyword begins
'Syntax is: InStr( [start position], string, substring, [1 for text comparison] )
Sheets(ReportYear).Select
CharacterPosition = InStr(PositionCounter, UCase(Cells(FoundRow, FoundColumn)), UCase(Keyword), 1)

If CharacterPosition > 0 Then
'Record location of keyword on the Keyword Search sheet
Sheets("WKS").Select
results_rowcounter = results_rowcounter + 1
Cells(results_rowcounter, 5).Value = FoundRow
Cells(results_rowcounter, 6).Value = FoundColumn
Cells(results_rowcounter, 7).Value = CharacterPosition
Cells(results_rowcounter, 8).Value = "R" & period_rowcounter_start & "C3:R" & period_rowcounter_end & "C3" 'format of R10C3:R13C3
Cells(results_rowcounter, 9).Value = ReportYear & "!R" & FoundRow & "C" & FoundColumn 'format of PastedAnnualReport!R100C8
End If

PositionCounter = CharacterPosition + 1
Wend
Else
isKeywordFound = False 'exit the loop because no new keyword was found
End If

Wend

Sheets("WKS").Select
Range("A1").Select
'return the last row of results (results_rowcounter)
FindKeyword = results_rowcounter

 

[ismii]

FindAll VBA Function

That link should help you with "Find All", but you'll need to make some improvement to allow the looping through multiple workbooks.
The above is the most in-depth look that I know of for this, so you may want to check out the below links for some not-as-in-depth looks.

Find All Instances With VBA — The Spreadsheet Guru

Find all matches in workbook using Excel VBA - Stack Overflow

And again, you'll have to expand them out to different workbooks.


Also, please use the tags for CODE around the code that you publish here.

 The code will then be in a box like this and look nice.

 

[ismii]

Also, something like Agent Ransack might be a tool you could use.
I believe Microsoft can search inside files as well.