VBA - Rolling out to a workbook and finding data

From my macro-enabled workbook, I want to open another workbook, and then use the "find" feature to locate a drug name. Part one, open the workbook works fine, Part two, find the data, was derived from "recording a macro", fails. What am I doing wrong?

'This coding opens the drug list workbook
Sub cmdDrugsRev_Click()
Path = ThisWorkbook.Path & "\"
Application.Workbooks.Open(Path & "HLDrugList.xlsx").Activate

'This coding asks for input, then finds the drug
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

End Sub

I get a error on running "Run time error 1104,Activate method of Range find failed"
Should I be using a UserForm??

bpascal123's picture

VBA - Rolling out to a workbook and finding data


I'm not sure what a user form will do better than the code you're providing.

Else, the find method will work best using a range object

dim Found as range
set Found = cells.Find(What:=c, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _


I haven't ran it but it should help you make your code work as you want.

The find method is well described on this site


Macro OpenAndFindTheDrug

It is not clear what do you want, but if you are looking for a name on the first column of the active worksheet and you want the system to return you the correspondent value on the second column, you can use this:

Sub OpenAndFindTheDrug()

Path = ThisWorkbook.Path & "\"
Application.Workbooks.Open(Path & "HLDrugList.xlsx").Activate

Dim DrugName As String
Dim MyRange As Range
Dim Ans As Variant

Set MyRange = Worksheets("sheet1").Cells
DrugName = InputBox("Insert the DrugName")
Ans = WorksheetFunction.VLookup(DrugName, MyRange, 2, False)
MsgBox Ans

End Sub

If you want something different please answer me back.

I hope it helps,
Cátia Santos