Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

When I search, certain fields comeback subscriptions out of range

0

Update!!! I have found my error. It took me two days but I found it...I watched the youtube video titled "Fast search entire excel workbook & return all results into a dashboard" using that Macro I have four fields two of them return subscript out of range, the Address and Invoice#, no matter what I put into them and I am a newb at Macros so the problem is probably very obvious. I have attached a sample of my database. Thank you 

Answer
Discuss

Answers

1
Selected Answer

I went through your code, commented it and made some corrections. Here is the present status. Please take note of my remarks.

Option Explicit             ' ALWAYS use Option Explicit
                            ' Tools > Options > Editor:-
                            ' Check "Require variable declarations" to set the option automatically

Sub Data_Search()
    ' always indent the entire content of procedures so that
    ' you can always see their beginning and end clearly

    Dim Search As Worksheet
    Dim Dashboard As Worksheet
    Dim Ws As Worksheet
    Dim Tbl As ListObject                   ' this is a worksheet table
    Dim Rng As Range
    Dim dataArray As Variant
    Dim dataToShowArray As Variant
    Dim dataColumnStart As Long
    Dim dataColumnEnd As Long
    Dim dataColumnWidth As Long             ' misleading: suggest dataColumnCount
    Dim dataRowStart As Long
    Dim SearchDataColumnStart As Long
    Dim SearchDataRowStart As Long
    Dim searchValue As Variant
    Dim searchField As Integer
    Dim FieldValue As Variant
    Dim R As Long                           ' R for "Row"
    Dim C As Long                           ' C for "Column"
    Dim i As Long                           ' i for "index"
    Dim NextRow As Long

    Application.ScreenUpdating = False 'Turning off for the macro can speed things up - not so much here though.

    ' illogical: "Search" is declares as "Worksheet", not as "Sheet"
    '            "Sheets" is a bigger collection incl sheets that aren't worksheets
    Set Search = Sheets("Search")       ' = Worksheets("Search")

    Set Tbl = Worksheets("Blank").ListObjects(1)
    dataColumnStart = 1
    dataColumnEnd = 15
    dataColumnEnd = Tbl.ListColumns.Count
'    dataColumnWidth = dataColumnEnd - 1
    dataColumnWidth = dataColumnEnd - dataColumnStart   ' probably illogical
    dataRowStart = 2
    dataRowStart = Tbl.DataBodyRange.Row
    ' all of the above appears unnecessary.
    ' Consider referring to Tbl.DataBodyRange which starts in row 2 of Tbl.Range

    ' Consider the use of named ranges in your worksheet.
    ' Syntax, like, Range("SearchCriterium").Value
    searchValue = Search.Range("D5").Value
    FieldValue = Trim(Search.Range("F5").Value)
    SearchDataColumnStart = 2   ' Column for the data on the Search
    SearchDataRowStart = 17

    'Figure out by which field we will search.
'    If (FieldValue = "Name") Then
'        searchField = 2
'    ElseIf (FieldValue = "Adress") Then
'        searchField = 3
'    ElseIf (FieldValue = "Phone") Then
'        searchField = 5
'    ElseIf (FieldValue = "Invoice#") Then
'        searchField = 7
'    End If
    Select Case FieldValue
        Case "Name"
            searchField = 2
        Case "Adress"
            searchField = 3
        Case "Phone"
            searchField = 5
        Case "Invoice#"
            searchField = 7
        Case Else
            MsgBox "You have selected an unlisted field name.", _
                   vbInformation, "Invalid search criteria"
            Exit Sub
    End Select

    'Loop through the worksheets [in ThisWorkbook - by default]
    For Each Ws In Worksheets
        'Ignore the Dashboard worksheet
        If (Ws.Name <> "Search" And Ws.Name <> "Blank") Then
            With Ws
                Set Rng = .Range(.Cells(dataRowStart, dataColumnStart), _
                                 .Cells(.Rows.Count, dataColumnStart).End(xlUp)) _
                                 .Resize(, dataColumnEnd)
            End With
            dataArray = Rng.Value
            ' consider this alternative:
            dataArray = Ws.ListObjects(1).DataBodyRange.Value

            ReDim dataToShowArray(1 To UBound(dataArray, 1), 1 To UBound(dataArray, 2))
'            ' Row increment for the final data array (datatoShowArray).
'            i = 1
            ' if i is reset here it can't continue counting entries
            ' extracted from the next worksheet. Entry #1 gets over-written.
            ' I prefer i to be a natural zero at the beginning of the proc.
            ' and increment only after something has been found.
            ' In this way i reflects the actual number of entries matched.

            'Loop through the rows in the data range.
            For R = 1 To UBound(dataArray, 1)
                'Check if the value in the row equals our search value
                If (dataArray(R, searchField) = searchValue) Then
                    'MATCH FOUND! Now do something!
                    i = i + 1           ' Increment the counter for the datatoShowArray
                    'Loop through the columns in the data range so can get their values.
                    For C = 1 To UBound(dataArray, 2)
                        'Add values to the array that will be used to put data into the Dashboard.
                        dataToShowArray(i, C) = dataArray(R, C)
                    Next C
                    ' must you continue looking at all names after a match has
                    ' been found in a Ws?
                    ' Exit For          ' search has been successful
                End If
            Next R
        End If
    Next Ws

    If i Then
        'Clear Dashboard
        Clear_Data Search, SearchDataColumnStart    ' pass arguments

        'Find next empty row in the Search.
        NextRow = Search.Cells(Search.Rows.Count, SearchDataColumnStart).End(xlUp).Row + 1
        ' you have cleared Search. Therefore the row is a constant (declared as variable here)
        ' The variable NextRow isn't required at all.
        NextRow = SearchDataRowStart

        'Put data into the dashboard.
        'Format = Range(Cells(1,1),Cells(2,2)).Value = datatoShowArray
        With Search
            Set Rng = .Range(.Cells(NextRow, SearchDataColumnStart), _
                             .Cells(NextRow + UBound(dataToShowArray, 1) - 1, SearchDataColumnStart + dataColumnWidth))
            ' consider this :
            Set Rng = .Cells(NextRow, SearchDataColumnStart).Resize(UBound(dataToShowArray, 1), _
                                      UBound(dataToShowArray, 2))
            ' still easier with Tbl.DataBodyRange
        End With
'        Search.Range(Cells(NextRow, SearchDataColumnStart), Cells(NextRow + UBound(dataToShowArray, 1) - 1, _
'                     SearchDataColumnStart + dataColumnWidth)).Value = dataToShowArray
        Rng.Value = dataToShowArray
    End If

    Application.ScreenUpdating = True       'Turn it back on at the end of the macro!
    MsgBox IIf(i, i, "No") & " matching record" & IIf(i > 1, "s were", " was") & " found.", _
           vbInformation, "Search result"
End Sub

Private Sub Clear_Data(Ws As Worksheet, ByVal SearchDataColumnStart As Long)
    'Search sheet

    Dim SearchDataRowStart As Long

    'Data table information
    SearchDataRowStart = 17

    ' ClearContents leaves the formatting in place
    With Ws
        ' both Rows and Columns must be counted in the same worksheet
        ' hence the leading periods here which you have omitted.
        ' Note: Columns.Count = > 16000. Consider Ws.UsedRange.Columns.Count
        .Range(.Cells(SearchDataRowStart, SearchDataColumnStart), .Cells(.Rows.Count, .Columns.Count)).Clear
    End With

' consider: having a table in that sheet
'    Ws.ListObjects(1).DataBodyRange.ClearContents
End Sub

To tell the truth, I corrected your code until it couldn't work anymore - and then I gave up :-). I found that I don't know what you want. Start with my remark at the end of the first inside loop that you might not need to continue searching after a match has been found. That is definitely so when you search for an Invoice# because invoice numbers should be unique but I don't  know if you would want to list recurring Names. Your code can't do that and when I tried to make mine do it I ran into complications which I didn't want to deal with without knowing your intention - and then maybe not, either. It would appear that the exit from the loop must be dependent upon the search field.

For now, the DataToShowArray is re-dimensioned for every worksheet. That doesn't match the index variable (i) that counts results through all worksheets the way I made it. But if you want to collect results from multiple worksheets the dimension of DataToShowArrayis wrong. The last dimension (UBound, 2) should be an arbitrary high number and UBound,2 be reset to the actual number of records as counted before pasting the result. But this can only be done for the last vector, meaning you would have to switch vectors when you transfer data from dataArray, and then use Rng.Value = Application.Transpose(dataToShowArray) in the end. 

It's a lot of work - and this wasn't even your question. Your question has a solution that is unrelated to VBA - quite funny actually after all the effort that went into checking the code, but I needed to understand the code to find the problem. The problem is caused by two spelling errors. "Invoice#" is misspelled in the drop-down list and "Address" is misspelled in the code. I have added a MsgBox to the code which would alert you to such irregularities.

Discuss

Discussion

Wow thank you so much.
Dgzanetti (rep: 2) Mar 6, '20 at 11:21 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login