Find in Entire Workbook 1

[PWD]

Hi. I'm trying to find something, somewhere on some worksheet and have got so far but not far enough. I've been having a look around the threads and this code seems to work (in Excel 2000 - on a small test workbook) to find something on the same worksheet and use the value in the next cell to paste back next to the selected cell:-

Sub TestFindCell()
Dim c As Range

Set c = Cells.find(What:=ActiveCell.Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False)
MyRow = c.Row 'Just for my reference so I can see what's going on when I step through it.
MyColumn = c.Column 'Ditto

    With (Cells(MyRow, MyColumn + 1)) 'Copies then pastes at the original location
    .Copy Destination:=Cells(ActiveCell.Row, ActiveCell.Column + 1)
    End With
    

End Sub

So far, so good.
But I want to be able to find it on any sheet (it should only occur once but could be anywhere).

So, I tried the following from another thread (just to see if it would display anything):-

Sub SearchBook2()
Dim ws As Worksheet
Dim sRes As String
Dim Found As Range

For Each ws In ThisWorkbook.Worksheets
    Set Found = ws.Cells.find(ActiveCell.Value)
        If Not Found Is Nothing Then
            sRes = ActiveCell.Value & " found in worksheet " & ws.Name & " cell " & Found.Address
            Exit For
        End If
Next
If sRes = "" Then sRes = ActiveCell.Value & " can't be found in this workbook"
MsgBox sRes
End Sub

Unfortunately I just get the 'can't be found' message.

The other funny thing is that when I F8 step through the code it appears to loop through the For/Next 7 times although there are only 3 worksheets. Not sure if that's significant.

What we are trying to achieve is a single summary sheet of products that updates from many different product category worksheets containing non-contiguous data.

I'm so close I can smell success - but oh so far. A military friend once told me that, "Nearly doesn't count, except in Horse-shoes and hand grenades."

I'm sure someone can help me out.

Many thanks,
Des.

 

[SkipVought]

Hi,

works for me.

Are you sure that you are referencing the correct WORKBOOK? Sounds to me like you have more than one open.

Skip,

_[red][/red]

 

[PWD]

Possibly, as I run all my macros from Personal.xls although I make sure that I'm in the correct cell of the correct workbook. I've just tested it from within Personal.xls and although I get the 'found' message it only finds the ActiveCell reference then stops. I've tried putting 'After:=ActiveCell' in the find but it makes no difference. If I remove the 'Exit For' it will continue and loop through all the worksheets and find the second occurence but I just can't get it to paste the value. It's as if

With (Cells(MyRow, MyColumn + 1)) 'Copies then pastes at the original location
    .Copy Destination:=Cells(ActiveCell.Row, ActiveCell.Column + 1)

only works on the same worksheet, because when I hover the mouse over the word 'Cells' when I'm stepping through the code, the value of (MyRow, MyColumn + 1) appears to correspond with the cell in whatever worksheet has the focus. So I'm assuming that as I start off in Sheet1, even though it finds what I want on another sheet at other cell references it is still focussing on Sheet1.

Yes. I've just tested this by putting a value in the (MyRow, MyColumn + 1) cell on Sheet1 and that's what gets pasted in.

I've put in a 'Count' to get over the first find being the ActiveCell but it's still the focus that's the problem. This is my current code:-

Sub TestDesFindCell2()
Dim Found As Range
Dim ws As Worksheet
Dim count

count = 0

For Each ws In ThisWorkbook.Sheets
     With ws.Cells
     
Set Found = ws.Cells.find(What:=ActiveCell.Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False)
        If Not Found Is Nothing Then
        If count = 0 Then GoTo line10

MyRow = Found.Row
MyColumn = Found.Column

    With (Cells(MyRow, MyColumn + 1)) 'Copies then pastes at the original location
    .Copy Destination:=Cells(ActiveCell.Row, ActiveCell.Column + 1)
    End With
        End If
line10:
count = count + 1
    End With
            Next

End Sub

Any ideas on how I can get the focus on the sheet that the data was found on so that it can be copied?

Many thanks, Des.

 

[SkipVought]

"...I run all my macros from Personal.xls ..."

Then ThisWorkbook is Personal.xls!

Skip,

_[red][/red]

 

[PWD]

Sure, sure. But I'm now doing this testing within Personal.xls. What can I do about this 'focus' on the original selected sheet? Once I get that solved I'll just attach the macro to the relevant workbook.

Thanks, Des.

 

[SkipVought]

Look at the After: Reference...

Sub TestDesFindCell2()
    Dim Found As Range
    Dim ws As Worksheet
    Dim count
    
    count = 0
    
    For Each ws In ThisWorkbook.Sheets
         With ws.Cells
         
            Set Found = ws.Cells.Find( _
                What:=ActiveCell.Value, _[b]
                After:=ws.[A1], _[/b]
                LookIn:=xlFormulas, _
                LookAt:=xlPart, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=False)
            If Not Found Is Nothing Then
                If count > 0 Then
                
                    With Found.Offset(0, 1) 'Copies then pastes at the original location
                        .Copy Destination:=ActiveCell.Offset(0, 1)
                    End With
                End If
            End If
            count = count + 1
        End With
    Next

End Sub

Skip,

_[red][/red]

 

[PWD]

Most excellent!! As long as I have the results on the first sheet, which kinda makes sense, then it just rips right through. You also tidied up the macro and made it neater too!!

Many thanks Skip.

Best regards, Des.

 

[SkipVought]

Glad I could help

Skip,

_[red][/red]