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()
' TeachExcel.com
' 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: https://docs.microsoft.com/en-us/office/vba/api/excel.range.find
Set Rng = dataIdCol.Find(What:=searchValue, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False)
' 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
Else
' Nothing found, tell the user.
MsgBox "Record not found."
End If
End If
End Sub