Sub Test__Navigate_To_Offset_Of_Search()
Call Navigate_To_Offset_Of_Search("next")
End Sub
Sub Navigate_To_Offset_Of_Search(previousOrNext As String)
Application.EnableEvents = False
Dim destinationAddress$
If UCase(previousOrNext) = "NEXT" Then
destinationAddress = Next_Result_Address
Else
destinationAddress = Previous_Result_Address
End If
Range( _
Range(destinationAddress), _
Range(destinationAddress).Offset(Row_Offset, Column_Offset) _
).Select
With ActiveWindow
.zoom = True
.zoom = Round(.zoom * 0.5, 0)
End With
Range(destinationAddress).Offset(Row_Offset, Column_Offset).Select
Call Record_Last_Visited_Index(destinationAddress)
Application.EnableEvents = True
End Sub
Sub Test__Record_Last_Visited_Index()
Call Record_Last_Visited_Index("B7")
End Sub
Sub Record_Last_Visited_Index(visitedCellAddress As String)
Range(Cell_Address_To_Keep_Track_Of_Last_Visited_Index).Value = Index_Of_Occurrence(Range(Cell_Address_To_Keep_OccurrenceList).Value, visitedCellAddress)
End Sub
Sub Test__Number_Of_Occurrences()
MsgBox Number_Of_Occurrences
End Sub
Function Number_Of_Occurrences()
Number_Of_Occurrences = Number_Of_Occurrences_In_Str(Range(Cell_Address_To_Keep_OccurrenceList).Value, ",") + 1
End Function
Sub Test__Previous_Result()
MsgBox Previous_Result
End Sub
Function Previous_Result_Address()
Dim previousIndex%
With Range(Cell_Address_To_Keep_Track_Of_Last_Visited_Index)
If .Value < 2 Then
previousIndex = Number_Of_Occurrences
Else
previousIndex = .Value - 1
End If
End With
Previous_Result_Address = Split(Range(Cell_Address_To_Keep_OccurrenceList).Value, ",")(previousIndex - 1)
End Function
Sub Test__Next_Result_Address()
MsgBox Next_Result_Address
End Sub
Function Next_Result_Address()
Dim nextIndex%
With Range(Cell_Address_To_Keep_Track_Of_Last_Visited_Index)
If .Value = Number_Of_Occurrences Then
nextIndex = 1
Else
nextIndex = .Value + 1
End If
End With
Next_Result_Address = Split(Range(Cell_Address_To_Keep_OccurrenceList).Value, ",")(nextIndex - 1)
End Function
Sub Test__Index_Of_Occurrence()
Debug.Print Index_Of_Occurrence("B1,B11,B35", "B11")
End Sub
Function Index_Of_Occurrence(list As String, cellAddress As String)
If InStr(list & ",", cellAddress & ",") = 0 Then
Index_Of_Occurrence = -1
Exit Function
End If
Index_Of_Occurrence = Number_Of_Occurrences_In_Str(SubString(list & ",", 1, InStr(list & ",", cellAddress & ",")), ",") + 1
End Function
Sub Record_Matched_Cells()
Dim matchedCells$
matchedCells = Matched_Cells
If matchedCells <> "" Then Range(Cell_Address_To_Keep_OccurrenceList).Value = matchedCells
End Sub
Function Matched_Cells()
'Match is NOT case-sensitive.
ThisWorkbook.Activate
Dim sheetName$, searchColumnLetter$, firstDataRow&, lastRow&, search$
Dim lookupRangeAddress$, matchedRow$, allMatches$, numberOfMatches%
sheetName = ActiveSheet.Name
firstDataRow = First_Data_Row
searchColumnLetter = Column_Of_Names
search = Trim(Range(Criteria_Cell).Value)
lastRow = Last_Non_Blank_Row_In_This_Column(sheetName, searchColumnLetter)
lookupRangeAddress = searchColumnLetter & firstDataRow & ":" & searchColumnLetter & lastRow
matchedRow = Excel_Match(sheetName, search, lookupRangeAddress, firstDataRow)
If matchedRow <> "" Then
allMatches = searchColumnLetter & matchedRow
Else
Matched_Cells = ""
Exit Function
End If
numberOfMatches = 1
Do While matchedRow <> ""
DoEvents 'Here just in case of an infinit loop, it won't crash Excel when you press Esc to quit.
matchedRow = Excel_Match(sheetName, search, searchColumnLetter & matchedRow + 1 & ":" & searchColumnLetter & lastRow, matchedRow + 1)
If matchedRow <> "" Then
numberOfMatches = numberOfMatches + 1
allMatches = allMatches & "," & searchColumnLetter & matchedRow
Else
GoTo Done
End If
Loop
Done:
Matched_Cells = allMatches
End Function
Function Excel_Match(sheetName As String, search As String, lookupRangeAddress As String, firstDataRow As Long)
On Error GoTo Exit_Function
If Exact_Match = True Then
Excel_Match = Application.WorksheetFunction.Match(search, Sheets(sheetName).Range(lookupRangeAddress), 0) + firstDataRow - 1
Else
Excel_Match = Application.WorksheetFunction.Match("*" & search & "*", Sheets(sheetName).Range(lookupRangeAddress), 0) + firstDataRow - 1
End If
Exit_Function:
End Function
Sub Test__Last_Non_Blank_Row_In_This_Column()
MsgBox Last_Non_Blank_Row_In_This_Column(ActiveSheet.Name, 2)
End Sub
Function Last_Non_Blank_Row_In_This_Column(sheetName As String, columnLetterOrNumber As Variant)
Last_Non_Blank_Row_In_This_Column = Sheets(sheetName).Cells(Sheets(sheetName).Rows.Count, columnLetterOrNumber).End(xlUp).Row
End Function
Sub Test__Number_Of_Occurrences_In_Str()
MsgBox Number_Of_Occurrences_In_Str("xx^2+6x+9", "x")
End Sub
Function Number_Of_Occurrences_In_Str(expression As String, character As String)
Number_Of_Occurrences_In_Str = (Len(expression) - Len(Replace(expression, character, ""))) / Len(character)
End Function
Sub Test__SubString()
MsgBox "|" & SubString("ABCDEF", 3, 5) & "|"
End Sub
Function SubString(inputString As String, start As Integer, finish As Integer)
'Only when b in Mid(inputString, a, b) is = Len(inputString) are SubString() and Mid() equivalent!
On Error Resume Next
SubString = Mid(inputString, start, finish - start + 1)
End Function
