Help with searching data in a different workbook and return the found record into my form VBA


Good day,

I'm using the macro shared on the website here to search for data and return the records to a form, however, I want the the VBA to search in a different workbook, not the same one as the form. I tried the below code but it's doesn't seem to work correctly, it opens the other workbook but then stops and gives an error msg "subscript is out of range (Error 9)"

Sub Select_Data()
' Search the data repository worksheet and return the found record into our form.

Dim sourceSheet As Worksheet
Dim dataSheet As Worksheet
Dim searchValue As Variant
Dim dataIdCol As Range
Dim recordRow As Integer

Dim wb As Workbook

Set wb = Workbooks.Open(Filename:="C:\Desktop\Total Amounts.xlsx")

' Make some sheet variables so we can use those instead of hard-coding sheet references in the code.
Set sourceSheet = ActiveWorkbook.Sheets("Rows")
Set dataSheet = Workbooks("Total Amounts.xlsx").Sheets("Sheet1")

' Column that contains the value for which we will search.
Set dataIdCol = dataSheet.Range("B:B")

' Value to search for.
searchValue = Sheets("Main UI").Range("D2").Value

' Check if the user input a value and hit the OK button.
If searchValue <> vbNullString Then
    'Value input, so search for it.

    'Clear Data
    sourceSheet.Rows("9").Value = ""

    ' Search
    ' More specific info:
    Set Rng = dataIdCol.Find(What:=searchValue, _
            LookIn:=xlValues, _
            LookAt:=xlWhole, _
            SearchOrder:=xlByRows, _

    ' Check if a record was found.
    If Not Rng Is Nothing Then
        ' Record found!

        ' Get the row of the record.
        recordRow = Rng.Row

        ' Put the records information back into the form.
        sourceSheet.Rows("9").Value = dataSheet.Cells(recordRow, 2).EntireRow.Value

      Workbooks("Total Amounts.xlsx").Close savechanges:=False

        ' Nothing found, tell the user.
        MsgBox "Record not found."

    End If

End If

End Sub


Selected Answer

You don't say in which line of your code the error occurs. However, this line would have that capability.

searchValue = Sheets("Main UI").Range("D2").Value

As you see, no workbook is specified. Therefore Sheets("Main UI") will be expected in the ActiveWorkbook. If it isn't found there the error will be of the "subscript out of range" type.

Speaking more generally, you lose control of your workbooks at the very beginning of the code, in fact, when you declare variables. Please consider:-

  1. When the code starts, it's likely (but not necessarily so) that ThisWorkbook is the ActiveWorkbook. Since you intend to change the ActiveWorkbook while your code is running your first move must be to sort out this discrepancy. 
    Dim Wb As Workbook   ' my rule: Wb is always the one requiring no explanation
    Dim WbData as Workbook
    Set Wb = ThisWorkbook
    Set WbData = Workbooks.Open(Filename:="C:\Desktop\Total Amounts.xlsx")
  2. After this clarification both your following lines of code turn out to be wrong.
    Set dataSheet = Workbooks("Total Amounts.xlsx").Sheets("Sheet1")
    Set sourceSheet = ActiveWorkbook.Sheets("Rows")
    Instead you should reference the workbooks by their names.
    Set dataSheet = WbData.Worksheets("Sheet1")
    Set sourceSheet = Wb.Worksheets("Rows")

I suggest you learn the difference between the "Sheets" and "Worksheets" collections. Your use of the Sheets collection is perfectly OK on only one condition, to wit, provided that you actually know the difference.


Answer the Question

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