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]

You can use the code to check if the workbook is already open or not:

'set workbook to variable
On Error Resume Next
Set WB = Workbooks("Machine History File")
On Error GoTo 0

'if workbook is not open (above code failed)
If WB Is Nothing Then
    'open workbook and set to variable
    Set WB = Workbooks.Open("H:\Machine History File.xls")
End If

As far as the searching goes, you can't search for a range like that. What are you wanting to do? Check each of the cells in the range individually?

 

[burdie87]

Hi,

I am trying to find a serial number from a cell in the active worksheet in a cell in a different worksheet in a different workbook( the workbook we are checking is open and if not opening it) at current the user has to copy and paste the serial number then go to the new workbook and do a ctrl + F then do a find manually which returns what I require.
Any help would be greatfully recieved.

 

[Legacy 68668]

try

Sub test()
Dim wb As Workbook, ws As Worksheet, r As Range, rng As Range
Set rng = ActiveSheet.Range("R12:U12")
On Error Resume Next
Workbooks("Machine History File.xls").Name = "Machine History File.xls"
If Err <> 0 Then Workbooks.Open("H:\Machine History File.xls")
On Error GoTo 0
For Each r In rng
    For Each wb In Workbooks
        For Each ws In wb.Sheets
            Set r = ws.Cells.Find(r.Value,,xlFormulas, xlPart)
            If Not r Is Nothing Then
                r.Offset(, 1).Value = "ok"
                Exit For
            End If
        Next
    Next
Next
End Sub

 

[burdie87]

Hi,

Thanks for your reply.

I have just tried the code but it comes up "can't assign to read only property" and highlights the .name part of the line of code below.

 Workbooks("Machine History File.xls").Name = "Machine History File.xls"

Any ideas?

Alan

 

[Legacy 68668]

Ooops

Sub test()
Dim wb As Workbook, ws As Worksheet, r As Range, rng As Range, x
Set rng = ActiveSheet.Range("R12:U12")
On Error Resume Next
x = Workbooks("Machine History File.xls").Name = "Machine History File.xls"
If Err <> 0 Then Workbooks.Open("H:\Machine History File.xls")
On Error GoTo 0
For Each r In rng
    For Each wb In Workbooks
        For Each ws In wb.Sheets
            Set r = ws.Cells.Find(r.Value,,xlFormulas, xlPart)
            If Not r Is Nothing Then
                r.Offset(, 1).Value = "ok"
                Exit For
            End If
        Next
    Next
Next
End Sub

 

[burdie87]

hi,

That has sorted that problem thanks but now it says "Object variable or with block variable not set"

it highlights

Set r = ws.Cells.Find(r.Value, , xlFormulas, xlPart)

any help appreciated.

 

[Von Pookie]

You need to use another variable there. The code is trying to use and reset the same variable in the same statement.

Try

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

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

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, , xlFormulas, xlPart)
        If Not R Is Nothing Then C.Offset(, 1).Value = "ok": Exit For
    Next Ws
Next C

Edit: It'd also be a good idea to reference what sheet you are using instead of relying on ActiveSheet. You would just put Sheet("SheetName") in place of ActiveSheet in that line of code where it is referencing the range.

 

[burdie87]

Hi,

Ok I have put the new code to work and do not get any errors but all it does is open my machine history file and thats it. It does not find the value in the cell in my first sheet.

I have tried removing the activesheet part and putting the sheet name in but no joy!

Any ideas?

 

[Von Pookie]

Something I didn't catch before, but it most likely the issue: the code is currently searching in formulas, not the values.

Change this

 Set R = Ws.Cells.Find(C.Value, , xlFormulas, xlPart)

to this

 Set R = Ws.Cells.Find(C.Value, , xlValues, xlPart

Also if you want it to only look at entire cell values--as opposed to any text within a cell (say if the number is in a sentence, it would pick that up), change xlPart to xlWhole.