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??
VBA - Rolling out to a workbook and finding data
Hi
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
-o-
dim Found as range
set Found = cells.Find(What:=c, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False).)
Found.select
-o-
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
http://www.cpearson.com/excel/findall.aspx
Pascal
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