Findnext in open workbooks

[farhan_babu]

Hi,

I m new in this forum.I have a problem that I have a code which is find through all open workbook but it is not woring as findnext.Here is the code.

[VBA]
Sub Find_Next()
On Error Resume Next
Dim wb As Workbook
Dim wk As Worksheet
Dim rng As Range
Dim rng1 As Range
Dim radd As String
For Each wb In Application.Workbooks
For Each wk In wb.Worksheets

Set rng = wk.Cells.Find(50, ActiveCell, xlFormulas, xlPart, xlColumns)
If Not rng Is Nothing Then
radd = rng.Address
'MsgBox wb.Name & wk.Name & rng.Address

Do
Set rng = wb.wk.Cells.FindNext(rng)
Loop While Not rng Is Nothing And rng.Address <> radd

Application.Goto rng, True
Exit For
Exit For
End If


Next
Next
End Sub
[/VBA]

Thanks in advance to help me.

Regards,

[Bob Phillips]

Post your workbook, it will make life a lot easier.

[johnske]

Hi farhan_babu,

Welcome to VBAX, hope you don't mind, but I've taken the liberty of putting your code in VBA tags to make it more readable.

Post your workbook, it will make life a lot easier.

(yes, good idea)

Regards,
John

[farhan_babu]

Hi,

This code is in personal macro book.May I do attachment?

Regards,

[Bob Phillips]

Yes, there is a button below the reply box called Manage Attachment.

[farhan_babu]

Here is the attachment.

[Bob Phillips]

There is not a lot of data on that worksheet, where is the data worksheet?

[farhan_babu]

Its book is for just example.Its run properly in find method through all open workbook but not working in case of FindNext method.Its dosn,t change the workbook or worksheet in case of FindNext.

I hope u will understand that.

REgards,

[jindon]

not sure if this is what you wanted....

Sub test()
Dim wb As Workbook, ws As Worksheet, r As Range, ff As String, msg As String
For Each wb In Workbooks
   For Each ws In wb.Sheets
     Set r = ws.Cells.Find(50, , , xlPart)
     If Not r Is Nothing Then
        ff = r.Address
        Do
           msg = msg & wb.Name & " : " & ws.Name & " : " & r.Address(0, 0) & vbLf
           Set r = ws.Cells.FindNext(r)
        Loop Until r.Address = ff
     End If
    Next
Next
If Len(msg) Then
    MsgBox "Found:" & vbLf & msg
Else
    MsgBox "Not found"
End If
End Sub

[farhan_babu]

Hi,

Thanks for your response but I want the following functionality;

1 - There is three workbooks open.

2 - And all books has the desired value of 50.

3 - When I first time run the code it should be select the first occurance in first workbook.

4 - When I run the 2nd time then the next sheet or book should be searched.

5 - When I run the Next time this code then it will search it next sheet or next book.

I hope I will explain my problem in detail.


Regards,

[jindon]

try

Private a()
Sub test()
Dim wb As Workbook, ws As Worksheet, r As Range, ff As String, n
On Error Resume Next
x = UBound(a, 2)
If Err.Number <> 0 Then
    On Error GoTo 0
    For Each wb In Workbooks
        For Each ws In wb.Sheets
            Set r = ws.Cells.Find(50, , , xlPart)
            If Not r Is Nothing Then
                ff = r.Address
                Do
                    n = n + 1
                    ReDim Preserve a(1 To 4, 1 To n)
                    a(1, n) = wb.Name: a(2, n) = ws.Name
                    a(3, n) = r.Address(0, 0)
                    Set r = ws.Cells.FindNext(r)
                Loop Until ff = r.Address
            End If
        Next
    Next
End If
For i = 1 To UBound(a, 2)
    If IsEmpty(a(4, i)) Then
        With Workbooks(a(1, i))
            .Activate
            With .Sheets(a(2, i))
                .Activate
                .Range(a(3, i)).Select
            End With
        End With
        a(4, i) = "n/a"
        flag = True
        Exit For
    End If
Next
If Not flag Then MsgBox "No more"
End Sub