![]()
Option Explicit Sub FindKeyword() Dim keywordSheet As Worksheet, _ ACTSHT As Worksheet, _ KeyWordListColumn As Variant, _ KWMatch As Variant, _ lastKeyWord As Long, _ VisibleCols As Long, _ VisibleRows As Long, _ KWsearch As String Set ACTSHT = ActiveSheet KWsearch = ActiveCell.Value If IsEmpty(ActiveCell) Then MsgBox "no word chosen" Exit Sub End If Set keywordSheet = ThisWorkbook.Worksheets("Keyword") keywordSheet.Activate Set KeyWordListColumn = Range("1:1").Find("keyword list") lastKeyWord = KeyWordListColumn.End(xlDown).Row Set KeyWordListColumn = KeyWordListColumn.Resize(rowsize:=lastKeyWord) Set KWMatch = KeyWordListColumn.Find(KWsearch) If Not KWMatch Is Nothing Then VisibleRows = ActiveWindow.VisibleRange.Rows.Count VisibleCols = ActiveWindow.VisibleRange.Columns.Count Application.Goto _ Reference:=KWMatch, _ scroll:=True ActiveWindow.SmallScroll _ Toleft:=VisibleCols \ 2 ActiveWindow.SmallScroll _ up:=VisibleRows \ 2 Else MsgBox KWsearch & vbCrLf & _ " was not found" End If 'match End Sub
Bookmarks