Consulting

Results 1 to 11 of 11

Thread: Findnext in open workbooks

  1. #1

    Findnext in open workbooks

    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,

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,149
    Location
    Post your workbook, it will make life a lot easier.

  3. #3
    Administrator
    Chat VP
    VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    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.

    Quote Originally Posted by xld
    Post your workbook, it will make life a lot easier.
    (yes, good idea)

    Regards,
    John
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

  4. #4
    Hi,

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

    Regards,

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,149
    Location
    Yes, there is a button below the reply box called Manage Attachment.

  6. #6
    Here is the attachment.

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,149
    Location
    There is not a lot of data on that worksheet, where is the data worksheet?

  8. #8
    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,

  9. #9
    VBAX Contributor
    Joined
    Jul 2005
    Posts
    169
    Location
    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

  10. #10
    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,

  11. #11
    VBAX Contributor
    Joined
    Jul 2005
    Posts
    169
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •