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

 

[burdie87]

Sorry feeling bad now but still no joy.

Just opens the machine history file but no search!

 

[Von Pookie]

Have you tried stepping-through the code? Instead of running it outright, press F8 to go through the code line by line and see what is happening.

 

[burdie87]

Hi Von,

Just tried it using F8. basically all is good until after the opening part. No errors in excuting the code when using f8 and it loops around the last parts as I presume it should.

For Each Ws In Wb.Worksheets
        Set R = Ws.Cells.Find(C.Value, , xlValues, xlWhole)
        If Not R Is Nothing Then C.Offset(, 1).Value = "ok": Exit For
    Next Ws
Next C

Not sure what else to do.

 

[Von Pookie]

Is it finding *anything*? Are you sure the number(s) you are looking for are correct? Just to be clear, if it does find anything, it will be putting the "ok" value on the original sheet containing the values to look up, assuming they are found.

Another thing I just noticed is that you probably want to change this

C.Offset(, 1).Value

to this

C.Offset(1, 0).Value

The way it is currently written, it will write the "ok" to the next column to the right if the value is found, and will overwrite anything that is there (presumably this would overwrite the next number you want to look up). Putting the 1 in the other spot will put it in the next row down, underneath the value you just looked up.

 

[Legacy 68668]

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.

OOps, again

Sub test()
Dim wb As Workbook, ws As Worksheet, r As Range, rng As Range, x
Dim c As Range
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 c = ws.Cells.Find(r.Value,,xlFormulas, xlPart)
            If Not c Is Nothing Then
                c.Offset(, 1).Value = "ok"
                Exit For
            End If
        Next
    Next
Next
End Sub

 

[burdie87]

Ok not sure if I have the wrong end of the stick here but all I want is for this sub to take me to the line containing the serial number it is searching for, as if I were manually opening the file pressing ctrl+F and had typed in the serial number and pressed OK.

I dont want it to type anything on the sheet as information will be overwritten which cannot happen.

Thanks Alan.

 

[burdie87]

Just realised it is working as far as it is writing ok in the cell to the right of the cell with the serial number in. However I said above I don't need the script to to write anything in the worksheet but simply take me to the cell with the serial in it.

Alan.

 

[Von Pookie]

Ok, then. I've been leaving it in doing the "ok" thing since that's what was in your original code. You didn't mention actually *going* to the cell before

In that case, let's try this

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, , xlValues, xlWhole)
        If Not R Is Nothing Then Application.Goto R : Exit Sub
    Next Ws
Next C

 

[Legacy 68668]

try

Sub test()
Dim wb As Workbook, ws As Worksheet, r As Range, rng As Range, x
Dim c As Range, txt As String, e
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 c = ws.Cells.Find(r.Value,,xlFormulas, xlPart)
            If Not c Is Nothing Then
                txt = txt & "," & wb.Name & "/" & _
                    ws.Name & "/" & c.Address
                Exit For
            End If
        Next
    Next
Next
If Len(txt) Then
    For Each e In Split(Mid(txt,2),",")
        y = Split(e, "/")
        Application.GoTo Workbooks(y(0)).Sheets(y(1)).Range(y(2)), True
        If vbYes <> MsgBox("Continue ?", vbYesNo + vbQuestion) Then Exit For
    Next
Else
    MsgBox "Not found"
End If
End Sub

 

[burdie87]

Thanks Von.

Works a treat now.

Final thing is it possible to make it check if anything is in R12:U12 and if blank to exit the sub so the search does'nt happen and it does'nt open up the machine history file?

Alan.