marcosis123
New Member
- Joined
- Dec 10, 2019
- Messages
- 12
- Office Version
- 365
- Platform
- MacOS
I have the below macro that searches an entire workbook for a particular string and returns that value from a cell a few cells down.
I now need it to search only worksheets that contain particular year, or range of years. So that it is out the way and kind of hidden im going to put the year in AA1, so i then need a way for the user to search only the worksheets containing a particular year in AA1 or a range, if possible, 2017-2019 for example.
Cheers for any help.
I now need it to search only worksheets that contain particular year, or range of years. So that it is out the way and kind of hidden im going to put the year in AA1, so i then need a way for the user to search only the worksheets containing a particular year in AA1 or a range, if possible, 2017-2019 for example.
Cheers for any help.
VBA Code:
Sub Return_Results_Entire_Workbook()
'This does not search the worksheet that will contain the results of the search
'Number for the worksheet that contains the value for which to search
searchValueSheet = "Search2 Test"
'Get the value for which we need to search into the macro
searchValue = Sheets(searchValueSheet).Range("A2").Value
'how many columns to the right of any "found" value that you want to use to return the data
returnValueOffset = -4
'The sheet where the results should be placed
outputValueSheet = "Search2 Test"
'The column in the sheet where the results should be placed
outputValueCol = 4
'The row in the sheet where the results should be placed
'everything from this row down must be empty!
outputValueRow = 4
'clear the results display area
Sheets(outputValueSheet).Range(Cells(outputValueRow, outputValueCol), Cells(Rows.Count, outputValueCol)).Clear
'count the worksheets in the workbook
wsCount = ActiveWorkbook.Worksheets.Count
'loop through the worksheets in the workbook
For i = 1 To wsCount
'Don't search the sheet with the lookup value or returned values - assumes source data will be on other tabs.
If i <> Sheets(searchValueSheet).Index And i <> Sheets(outputValueSheet).Index Then
'Perform the search, which is a two-step process below
Set Rng = Worksheets(i).Cells.Find(What:=searchValue, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
rangeLoopAddress = Rng.Address
Do
Set Rng = Sheets(i).Cells.FindNext(Rng)
Sheets(outputValueSheet).Cells(Cells(Rows.Count, outputValueCol).End(xlUp).Row + 1, outputValueCol).Value = Sheets(i).Range(Rng.Address).Offset(0, returnValueOffset).Value
Loop While Not Rng Is Nothing And Rng.Address <> rangeLoopAddress
End If
End If
Next i
End Sub