Search using cell value

[burdie87]

Hi Guys & Girls,

I am trying to cobble together a sub to use a cell value in my current workbook to search in a different workbook which may or may not need to be opened before the search can take place. (I have the code below which I have tried to piece together but this is a bit above my skill level.
Any help would be greatly appreciated.

Private Sub CommandButton36_Click()
Dim WB As Workbook, found As Range

WB = "H:\Machine History File.xls"
Tag = Range("R12:U12")

Set found = WB.Cells.Find(What:=Tag, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
If Not found Is Nothing Then
    ActiveCell.Offset(0, 1).Value = "ok"
End If

End Sub

 

[Von Pookie]

Yup, you sure can do that!

Dim Wb As Workbook, Ws As Worksheet
Dim Rng As Range, R As Range, C As Range

Set Rng = ThisWorkbook.ActiveSheet.Range("R12:U12")

If WorksheetFunction.CountA(Rng) = 0 Then Exit Sub

On Error Resume Next
Set Wb = Workbooks("Machine History File")
On Error GoTo 0
If Wb Is Nothing Then Set Wb = Workbooks.Open("H:\Machine History File.xls")

For Each C In Rng
    For Each Ws In Wb.Worksheets
        Set R = Ws.Cells.Find(C.Value, , xlValues, xlWhole)
        If Not R Is Nothing Then Application.Goto R : Exit Sub
    Next Ws
Next C

 

[burdie87]

Cheers Works A Dream