Searching an entire workbook for keywords and then printing the results on another worksheet I made a search page at the beginning of a work book. This page has a drop down that allows you to choose what category to search in, then you type your phrase or keyword into the search box and it finds all of the results. It then takes those results and pastes them in in the chart at the bottom of the search page. You then have the option to print out those results or clear the chart.  Pictures are also posted for better understanding. 

 

Private Sub ComboBox1_Change()

End Sub


Private Sub ComboBox2_Change()
    UpdateSearchBox
End Sub

Private Sub CommandButton1_Click()
    Select Case TextBox1.Value
        Case "F"
            TextBox1.Value = "G"
        Case "E"
            TextBox1.Value = "F"
        Case "D"
            TextBox1.Value = "E"
        Case "C"
            TextBox1.Value = "D"
        Case "B"
            TextBox1.Value = "C"
        Case "A"
            TextBox1.Value = "B"
        Case "G"
            TextBox1.Value = "A"
    End Select
End Sub

Private Sub CommandButton2_Click()
    FindOne
End Sub

Private Sub TextBox1_Change()
    UpdateSearchBox
End Sub

Sub UpdateSearchBox()
    Dim PageName As String, searchColumn As String, ListFiller As String
    Dim lastRow As Long

    If TextBox1.Value &lt;&gt; "" Then
        PageName = TextBox1.Value
    Else
        Exit Sub
    End If

    Select Case ComboBox2.Value
        Case "EQUIPMENT NUMBER"
            searchColumn = "A"
        Case "EQUIPMENT NAME"
            searchColumn = "C"
        Case "DUPONT NUMBER"
            searchColumn = "F"
        Case "SAP NUMBER"
            searchColumn = "G"
        Case "SSI NUMBER"
            searchColumn = "H"
        Case "PART NAME"
            searchColumn = "I"
        Case ""
            MsgBox "Please select a value for what you are searching by."
    End Select

    lastRow = Sheets(PageName).Range("A65536").End(xlUp).Row

        If lastRow &lt;&gt; 0 And PageName &lt;&gt; "" And searchColumn &lt;&gt; "" Then
        ListFiller = PageName &amp; "!" &amp; searchColumn &amp; "2" &amp; ":" &amp; searchColumn &amp; lastRow
        ComboBox1.ListFillRange = ListFiller
    End If
End Sub
Sub FindOne()

    Range("B19:J1500") = ""

    Application.ScreenUpdating = False

    Dim k As Integer, EndPasteLoopa As Integer
    Dim myText As String, searchColumn As String
    Dim totalValues As Long
    Dim nextCell As Range

    k = ThisWorkbook.Worksheets.Count
    myText = ComboBox1.Value
    Set nextCell = Range("B20")
    If myText = "" Then
        MsgBox "No Address Found"
        Exit Sub
    End If

    Select Case ComboBox2.Value
        Case "EQUIPMENT NUMBER"
            searchColumn = "A"
        Case "EQUIPMENT NAME"
            searchColumn = "C"
        Case "DUPONT NUMBER"
            searchColumn = "F"
        Case "SAP NUMBER"
            searchColumn = "G"
        Case "SSI NUMBER"
            searchColumn = "H"
        Case "PART NAME"
            searchColumn = "I"
        Case ""
            MsgBox "Please select a value for what you are searching by."
    End Select

    For i = 2 To k
        totalValues = Sheets(i).Range("A65536").End(xlUp).Row
        ReDim AddressArray(totalValues) As String

        For j = 0 To totalValues
            AddressArray(j) = Sheets(i).Range(searchColumn &amp; j + 1).Value
        Next j

        For j = 0 To totalValues
            If (myText = AddressArray(j)) Then
                EndPasteLoop = 1
                If (Sheets(i).Range(searchColumn &amp; j + 2).Value = "") Then EndPasteLoop = Sheets(i).Range(searchColumn &amp; j + 1).End(xlDown).Row - j - 1
                For r = 1 To EndPasteLoop
                    Range(nextCell, nextCell.Offset(0, 8)).Value = Sheets(i).Range("A" &amp; j + r, "I" &amp; j + r).Value
                    Set nextCell = nextCell.Offset(1, 0)
                Next r
            End If
        Next j
    Next i

    Application.ScreenUpdating = True
End Sub
Here are some of the changes I'd make


Use Option Explicit at the top of the module


Eliminate all errors found by compiling the code (Debug -> CompileVBA Project)
(declare all variables, first of all)

Remove the empty Sub ComboBox1_Change() declaration if you don't inded to use it
Replace string comparisons such as PageName &lt;&gt; "" with


PageName &lt;&gt; vbNullString (faster)
Len(PageName) &gt; 0 (even faster)

As pointed out in the comment, generalize the lastRow and totalValues statements


From:


  lastRow = Sheets(PageName).Range("A65536").End(xlUp).Row


To


With Sheets(PageName)
    lastRow = .Range(.Rows.Count, 1).End(xlUp).Row
End With





To reduce total lines of code and make it a bit easier to maintain I'd replace the three Select statements with one separate Function, and use it like this:

Private Sub CommandButton1_Click()
    TextBox1.Value = GetSelectedLetter(TextBox1.Value)
End Sub

Sub UpdateSearchBox()
    ...
    searchColumn = GetSelectedLetter(ComboBox2.Value)
    If InStr(searchColumn, "Please") &gt; 0 Then MsgBox searchColumn
    ...
End Sub

Sub FindOne()
    ...
    searchColumn = GetSelectedLetter(ComboBox2.Value)
    If InStr(searchColumn, "Please") &gt; 0 Then MsgBox searchColumn
    ...
End Sub


Public Function GetSelectedLetter(ByVal sel As String) As String
    Select Case sel
        Case "G", "EQUIPMENT NUMBER":   GetSelectedLetter = "A":    Exit Function
        Case "B", "EQUIPMENT NAME":     GetSelectedLetter = "C":    Exit Function
        Case "E", "DUPONT NUMBER":      GetSelectedLetter = "F":    Exit Function
        Case "F", "SAP NUMBER":         GetSelectedLetter = "G":    Exit Function
        Case "D":                       GetSelectedLetter = "E":    Exit Function
        Case "SSI NUMBER":              GetSelectedLetter = "H":    Exit Function
        Case "PART NAME":               GetSelectedLetter = "I":    Exit Function
        Case "C":                       GetSelectedLetter = "D":    Exit Function
        Case "A":                       GetSelectedLetter = "B":    Exit Function
        Case vbNullString:
            GetSelectedLetter = "Please select a value for what you are searching by."
    End Select
End Function


(not the standard indentation, but I find it visually easier to work with)



Performance

One improvement: start the search after moving all data to arrays (array of arrays specifically)

Example:

Public Sub SearchAllSheetsArrayOfArrays()
    Const SRC_VAL = "Test"

    Dim ws As Worksheet, arr() As Variant, i As Long, j As Long, tc As Long, tw As Long
    Dim r As Long, c As Long, lbr As Long, ubr As Long, lbc As Long, ubc As Long

    i = 1
    tw = ThisWorkbook.Worksheets.Count
    ReDim arr(i To i + tw - 1)  'Place all data from each sheet in Variant sub-arrays

    For Each ws In ThisWorkbook.Worksheets
        If ws.UsedRange.Cells.Count &gt; 1 Then
            arr(j + 1) = ws.UsedRange
            j = j + 1
        End If
    Next

    tw = j
    ReDim Preserve arr(i To tw) 'Trim the main Array if there are empty sheets

    For i = 1 To tw     'tw = total worksheets
        lbr = LBound(arr(i), 1): ubr = UBound(arr(i), 1)    'dimension 1 (rows)
        lbc = LBound(arr(i), 2): ubc = UBound(arr(i), 2)    'dimension 2 (cols)
        For r = lbr To ubr
            For c = lbc To ubc
                If Len(arr(i)(r, c)) &gt; 0 Then
                    If InStr(arr(i)(r, c), SRC_VAL) &gt; 0 Then tc = tc + 1 'collect vals
                End If
            Next
        Next
    Next
    Debug.Print tc
End Sub




Ps. I used very short variable names to make the code fit in its window without scroll bars, but more descriptive names should be used - like in your posted code, but try to keep naming conventions consistent: all variables start with lower case letters, all procedures start with upper case letters